SQL RANK() vs. ROW_NUMBER(): What's the Difference?

Differences between SQL RANK() and ROW_NUMBER()

When using SQL ranking functions, it is critical to understand the difference between RANK() and ROW_NUMBER(). While they may look similar in some cases, they may behave differently when dealing with duplicate values.

Comparison of ROW_NUMBER() and RANK()

ROW_NUMBER() assigns a consecutive integer value to each row, regardless of whether the values ​​in the sorting column are the same. This means that ROW_NUMBER() will always assign a unique integer to each row.

RANK(), on the other hand, assigns a rank to each row based on the row's sorted order within the partition. Rows with the same sort value will receive the same rank. When a tie occurs, this results in gaps in the rankings.

Demo

The following SQL query demonstrates the difference between ROW_NUMBER() and RANK():

SELECT ID, [Description], RANK() OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank' FROM SubStyle

SELECT ID, [Description], ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) as 'RowNumber' FROM SubStyle

Using these queries, both result sets initially look identical. However, if there is any tie in the rankings, the difference becomes apparent. Consider the following example data:

WITH T(StyleID, ID)
AS (SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 1,2)
SELECT *,
RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS [RANK],
ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS [ROW_NUMBER],
DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS [DENSE_RANK]
FROM T

Result:

<code>StyleID     ID       RANK      ROW_NUMBER      DENSE_RANK
----------- -------- --------- --------------- ----------
1           1        1         1               1
1           1        1         2               1
1           1        1         3               1
1           2        4         4               2</code>

In this example, ROW_NUMBER() will increment even if there is a tie, while RANK() will assign the same rank to all tied rows. DENSE_RANK() is like RANK(), but assigns the next different rank (2) to the next different ranking value.

The above is the detailed content of SQL RANK() vs. ROW_NUMBER(): What's the Difference?. For more information, please follow other related articles on the PHP Chinese website!