RANK(), DENSE_RANK(), and ROW_NUMBER()

RANK(), DENSE_RANK(), and ROW_NUMBER() are three common analytic functions in SQL that are used to generate rankings and row numbers for a given result set.

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row in the result set, regardless of any duplicates.

RANK()

RANK() assigns a unique rank to each distinct value in the result set based on the order specified in the ORDER BY clause. If there are multiple rows with the same value, they are assigned the same rank, and the next rank is then skipped. For example, if there are two rows with the same value, they will be assigned the same rank, and the next row will be assigned a rank equal to the number of rows processed so far plus one.

DENSE_RANK()

DENSE_RANK() is similar to RANK(), but it doesn’t skip any ranks. Instead, it assigns a unique rank to each distinct value in the result set based on the order specified in the ORDER BY clause, with no gaps between the ranks. For example, if there are two rows with the same value, they will be assigned the same rank, and the next row will be assigned a rank equal to the number of distinct ranks processed so far plus one.

Here’s an example to illustrate the difference between these functions:

SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) AS RowNum, 
       RANK() OVER (ORDER BY col1) AS Rank, 
       DENSE_RANK() OVER (ORDER BY col1) AS DenseRank
FROM table1;

In this example, we have a table table1 with a column col1. We use the three analytic functions to assign row numbers, ranks, and dense ranks to each row based on the values in col1. The ROW_NUMBER() function assigns a unique sequential number to each row, while the RANK() and DENSE_RANK() functions assign unique rankings based on the values in col1. The RANK() function may skip ranks, while the DENSE_RANK() function assigns a unique rank to each distinct value with no gaps between the ranks.