SQL Server Interview Question #94

What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

All three assign ranking values, but ties are handled differently.

ROW_NUMBER always assigns a unique sequential number. RANK gives equal values the same rank and leaves gaps after ties. DENSE_RANK also gives equal values the same rank but does not leave gaps.

For values 100, 100, and 90 ordered descending, ROW_NUMBER may produce 1,2,3; RANK produces 1,1,3; and DENSE_RANK produces 1,1,2.

Code Example

SELECT Name,
       Price,
       ROW_NUMBER() OVER (ORDER BY Price DESC) AS RowNo,
       RANK()       OVER (ORDER BY Price DESC) AS RankNo,
       DENSE_RANK() OVER (ORDER BY Price DESC) AS DenseRankNo
FROM dbo.Products;