Ranking function in SQL server
RANK()
- RANK() function returns number in sequence and it returns the same number when we have same value in column and skip (with gaps) the value from sequence like (1, 2, 2, 4, 4, 6, 7, 8)
DENSE_RANK()
- DENSE_RANK() function returns number in sequence and it returns the same number when we have same value in column and does not skip (no gaps) any number from sequence like(1, 2, 2, 3, 3, 4, 5, 6)
ROW_NUMBER()
- ROW_NUMBER() function generates the unique number in sequence
- based on specified order by condition on particular column for each rows without any gap and duplication. (1, 2, 3, 4, 5, 6, 7, 8)
Example:
SELECT
RANK() OVER(ORDER BY Age) AS Rank_number,
DENSE_RANK() OVER(ORDER BY Age) AS Dense_Rank_number,
ROW_NUMBER() OVER(ORDER BY Age) AS Rownumber_number,
* FROM Customers
Use of Partition by
- Partition by divides result and then ranking function are apply
Example:
SELECT
RANK() OVER(PARTITION BY Country ORDER BY Age) AS Rank_number,
DENSE_RANK() OVER(PARTITION BY Country ORDER BY Age) AS Dense_Rank_number,
ROW_NUMBER() OVER(PARTITION BY Country ORDER BY Age) AS Rownumber_number,
* FROM Customers
