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!