SQL SERVER ROW_NUMBER

SQL Server ROW_NUMBER returns sequential number of a row with in a partition list.

Row_Number must be used with OVER clause in SQL SERVER other wise
User will get error message "The function 'row_number' must have an OVER clause."

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Ex: Display salary in descending order for each department  using row_number

select row_number() over(partition by deptno order by sal desc) rownumber,e.* from emp e

O/P:
rownumber    EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
1    7839    KING    PRESIDENT    NULL    1981-11-17    5000.00    NULL    10
2    7782    CLARK    MANAGER    7839    1981-06-09    2450.00    NULL    10
3    7934    MILLER    CLERK    7782    1982-01-23    1300.00    NULL    10
1    7902    FORD    ANALYST    7566    1981-12-03    3000.00    NULL    20
2    7566    JONES    MANAGER    7839    1981-04-02    2975.00    300.00    20
3    7369    SMITH    CLERK    7902    1980-12-17    800.00    NULL    20
1    7698    BLAKE    MANAGER    7839    1981-05-01    2850.00    NULL    30
2    7499    ALLEN    SALESMAN    7698    1981-02-20    1600.00    300.00    30
3    7844    TURNER    SALESMAN    7698    1981-09-08    1500.00    0.00    30
4    7521    WARD    SALESMAN    7698    1981-02-22    1250.00    500.00    30
5    7654    MARTIN    SALESMAN    7698    1981-09-28    1250.00    1400.00    30
6    7900    JAMES    CLERK    7698    1981-12-03    950.00    NULL    30

Ex:get top 3 salaries from emp table using ROW_NUMBER

select * from  (select ROW_NUMBER() over(order by sal desc) rowindex,ename,sal from emp e) ee where rowindex <=3

O/P:
rowindex    ename    sal
1    KING    5000.00
2    FORD    3000.00
3    JONES    2975.00

Ex: get 5th salary from emp table using ROW_NUMBER

select * from  (select ROW_NUMBER() over(order by sal desc) rowindex,ename,sal from emp e) ee where rowindex =5

O/P:
rowindex    ename    sal
5    CLARK    2450.00

Tags:SQL SERVER ROW_NUMBER,SQL SERVER ROWN_NUMBER inline query,ROW_NUMBER with OVER Clause,ROW_NUMBER ORDER By CLAUSE,ROW_NUMBER PARTITION