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():

<code class="language-sql">SELECT ID, [Description], RANK() OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank' FROM SubStyle</code>

<code class="language-sql">SELECT ID, [Description], ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) as 'RowNumber' FROM SubStyle</code>

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:

<code class="language-sql">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</code>

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!