SQL RANK() vs. ROW_NUMBER(): How Do They Differ When Handling Tied Rows?

Differences between SQL RANK() and ROW_NUMBER()

The difference between the RANK() and ROW_NUMBER() functions in SQL often confuses developers. Although the provided SQL query statements may appear to produce the same results initially, they will run differently in the presence of identical values.

Dive into the differences

The key difference between the RANK() and ROW_NUMBER() functions is how they handle identical values. RANK() and its corresponding DENSE_RANK() function exhibit deterministic behavior. When multiple rows have the same value on the partitioning and sorting columns, they are assigned the same rank.

In contrast, ROW_NUMBER() assigns increasing result values ​​even for rows of the same value. This behavior is non-deterministic, which means that the order of identical rows in the result set may change arbitrarily.

Example description

Consider the following scenario: all rows share the same StyleID, forming a partition. Within this partition, the first three rows are equivalent when sorted by ID.

<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>

The result output is as follows:

<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>

As you can see, ROW_NUMBER() assigns numerical values ​​to identical rows incrementally, while RANK() and DENSE_RANK() assign the same rank to all three identical rows. DENSE_RANK() behaves like RANK(), but it assigns the next different value 2 to subsequent rows to avoid gaps in the ranking sequence.

The above is the detailed content of SQL RANK() vs. ROW_NUMBER(): How Do They Differ When Handling Tied Rows?. For more information, please follow other related articles on the PHP Chinese website!