ROW_NUMBER oracle

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;