How to use the ORDER BY clause in an inner MS SQL?
Sample Table:-
| emp_id | emp_name | job_name | dep_id |
| 68319 | KAYLING | PRESIDENT | 1001 |
| 66928 | BLAZE | MANAGER | 3001 |
| 67858 | SCARLET | ANALYST | 2001 |
| 63679 | SANDRINE | CLERK | 2001 |
sql:-
SELECT *
FROM (
SELECT *
FROM (
SELECT emp_id,
emp_name,
job_name,
dep_id,
ROW_NUMBER( ) OVER( ORDER BY dep_id ) AS 'sequence'
FROM employees
) opt
) newResult:-
| emp_id | emp_name | job_name | dep_id | sequence |
| 68319 | KAYLING | PRESIDENT | 1001 | 1 |
| 67858 | SCARLET | ANALYST | 2001 | 2 |
| 63679 | SANDRINE | CLERK | 2001 | 3 |
| 66928 | BLAZE | MANAGER | 3001 | 4 |
Description:-
we use ROW_NUMBER( ) OVER( ORDER BY `column_name` ) AS 'column_name'
In some inner query cases, we can’t use ORDER BY clause.
2 Comments
Luan
August 5, 2024Hi, MySQL doesn’t support ROW_NUMBER function. Please correct. In this example you can use SELECT * FROM employees ORDER BY dep_id
Your query gave wrong result.
Rohit Patra
August 10, 2024Thank You for replying.
This is an MS SQL which support `ROW_NUMBER`. In some cases we can’t use `ORDER BY` clause directly in an inner query.