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