Working with ROW_NUMBER, RANK, and DENSE_RANK

Window functions are an essential tool for data analysis, particularly in the SQL language. They allow you to perform complex calculations, like running totals, moving averages, and ranking values. In this blog, we’ll explore three popular ranking functions in SQL: ROW_NUMBER(), RANK(), and DENSE_RANK().

ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row in the result set. It’s the simplest of the three ranking functions, as it generates a sequence of integers, starting from 1 to each row based on the column mentioned in the order by clause. Here’s an example, suppose we have a table with two columns, Country and Sales.

SELECT 
       Country
       , Sales 
       , ROW_NUMBER() OVER (ORDER BY Sales DESC) AS RowNum
FROM 
       RetailSales

Here the ROW_NUMBER() function has assigned ranks starting from 1 based on the sales values.

RANK()

The RANK() function is similar to the ROW_NUMBER() function, but it assigns the same rank to two or more rows if they have the same value. Suppose two rows have the same rank then the next rank will be skipped, so you can see gaps in the sequence of the ranks.

Lets check an example to understand it more clearly.

SELECT
       Country
       , Sales 
       , RANK() OVER (ORDER BY Sales DESC) AS Rnk
FROM
       RetailSales

So here we can see that UK and Germany have rank 3 and Austria has rank 5 and rank 4 got skipped as we had two rows with rank 3.

DENSE_RANK()

The DENSE_RANK() function is similar to the RANK() function, the only difference here is that even if two or more rows have the same rank next rank is not skipped.

Here's an example.

SELECT 
       Country
       , Sales 
       , DENSE_RANK() OVER (ORDER BY Sales DESC) AS Dense_Rnk
FROM 
       RetailSales

So here similar to RANK() function we have same rank for UK and Germany which is 3 but for Austria the rank is 4, so the rank wasn’t skipped.

Conclusion

In conclusion, the ROW_NUMBER, RANK, and DENSE_RANK functions in SQL offer valuable functionality for assigning unique values or rankings to rows based on specific criteria.

ROW_NUMBER provides a unique sequential number to each row, while RANK assigns unique ranks with ties skipping subsequent ranks. DENSE_RANK assigns unique ranks with ties maintaining a continuous sequence.

By understanding the differences and use cases of these functions, SQL developers can effectively analyze and rank data, enabling informed decision-making in various business scenarios.