RANK()
The RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same values then each row with the same values will get the same rank numbers. However, after the same rank numbers or duplicate ranks next rank of the row will be skipped.
DENSE_RANK()
DENSE_RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same value then each of these rows will share the same rank. However, the dense_rank of the following rows will not get skipped.
ROW_NUMBER()
ROW_NUMBER() function will assign a unique row number to every row within each partitioned result set. It does not matter if the rows are duplicates or not.
Let’s look at the below sample table to explain the difference.
Table Name: Manager
id | name | salary |
---|---|---|
1 | Jeff | 8000 |
2 | Elon | 5000 |
3 | Mukesh | 5000 |
4 | Warren | 4000 |
5 | Bill | 3000 |
SELECT *, RANK() OVER( ORDER BY salary DESC) as ranks, DENSE_RANK() OVER( ORDER BY salary DESC) as dense_ranks, ROW_NUMBER() OVER( ORDER BY salary DESC) as row_numbers FROM manager;
id | name | salary | ranks | dense_ranks | row_numbers |
---|---|---|---|---|---|
1 | Jeff | 8000 | 1 | 1 | 1 |
2 | Elon | 5000 | 2 | 2 | 2 |
3 | Mukesh | 5000 | 2 | 2 | 3 |
4 | Warren | 4000 | 4 | 3 | 4 |
5 | Bill | 3000 | 5 | 4 | 5 |
0 Comments