ROW_NUMBER is an example of analytical function. It returns a unique number for each record.
Consider below emp table data
| EMPNO | EMPNAME | SALARY | DEPTNO |
| 10 | Bill | 12000 | 5 |
| 11 | Solomon | 10000 | 5 |
| 12 | Susan | 10000 | 5 |
| 13 | Wendy | 9000 | 1 |
| 14 | Benjamin | 7500 | 1 |
| 15 | Tom | 7600 | 1 |
| 16 | Henry | 8500 | 2 |
| 17 | Robert | 9500 | 2 |
| 18 | Paul | 7700 | 2 |
ROW_NUMBER()
Select empno, deptno, salary, row_number() over(order by salary) from emp;
ROW_NUMBER assigns a unique number by ordering by salary ascending by default
You can use row_number to find top N records
Below query returns first 3rd to 5th records
select * from
(
select empno, deptno, salary, row_number() over(order by salary) as rn from emp
) where rn between 3 and 5
;
ROW_NUMBER() with partition by clause
select empno, deptno, salary,
row_number() over(partition by deptno order by salary)
from emp;
Returns unique number within the window group, ie deptno. Each department it will find the employees based on their salary.
ROW_NUMBER() with NULLS LAST
This ignores the records having salary as null and give least priority for ranking
Select empno, deptno, salary,
row_number() over(order by salary nulls last)
from emp;
