Row_number, Rank & Dense_Rank function

Difference between ROW_NUMBER, RANK & DENSE_RANK analytical function:

Row_Number: Rank shows unique number for every records fetched by the SQL queries.

RANK: Rank generates the ranking number of every records of the qualified column; if the value of the qualified column is repeated then ranking number will be repeated & next ranking number will be skipped for every repeated value of the qualified column.

DENSE_RANK: Dense rank function generate the ranking number same as RANK function; only difference is that, ranking number is not skip for repeated values of the qualified column. Its generate a serial ranking number with repeated ranking for repeated values of the qualified column.

Connect default schema HR of ORACLE.

Execute below SQL & show the output difference of Row_number, Rank & Dense_Rank

SQL:

select department_id,e.last_name,e.salary

       ,row_number() over(order by e.salary desc) row_num

       ,rank() over(order by e.salary desc) rnk

       ,dense_rank() over(order by e.salary desc) Dens_rnk

from employees e;

Output:

SQL Output of Rank Query

Using Partition By Clause to determine a ranking per Department:

SQL:

select department_id,e.last_name,e.salary

,row_number() over(partition by department_id order by e.salary desc) row_num

,rank() over(partition by department_id  order by e.salary desc) rnk

,dense_rank() over(partition by department_id  order by e.salary desc)Dns_rnk

from employees e

where department_id in (80,50);

Output:

SQL Output with Partition By Clause