ROW_NUMBER Analytic Functions in Oracle SQL
ROW_NUMBER is assigned a unique number to each row starting from 1 in the ordered sequence of the query result.
It is mostly used in finding the top-N rows, inner-N and last-N rows by using sub query in reporting of data.
Syntax:
ROW_NUMBER( ) OVER ( partition by | Order by )
Example as ROW_NUMBER function
TOP-N: Fetch the 3 highest salaried employees of department id 50
select employee_id, salary from (
SELECT
employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary desc) rn
FROM employees where department_id = 50 )
where rn <= 3;
EMPLOYEE_ID SALARY ----------- ---------- 121 8200 120 8000 122 7900
BOTTOM-N: Fetch the 3 least salaried employees of department id 50
select employee_id, salary from (
SELECT
employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary asc) rn
FROM employees where department_id = 50 )
where rn <= 3;
EMPLOYEE_ID SALARY ----------- ---------- 132 2100 128 2200 136 2200
INNER-N: Fetch record in between position 3 to 5
select employee_id, salary from (
SELECT
employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary asc) rn
FROM employees where department_id = 50 )
where rn >= 3 and rn <= 5;
EMPLOYEE_ID SALARY ----------- ---------- 136 2200 127 2400 135 2400
Use of partition clause: Fetch department wise salary ranking
SELECT
department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
FROM employees;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY RN ------------- ----------- ---------- ------- ----- 10 Jennifer Whalen 4400 1 20 Michael Hartstein 13000 1 20 Pat Fay 6000 2 30 Alexander Khoo 12000 1 30 Den Raphaely 11000 2 30 Shelli Baida 2900 3 30 Sigal Tobias 2800 4 30 Guy Himuro 2600 5 30 Karen Colmenares 2500 6