SQL RANK() vs. ROW_NUMBER(): When Should You Use Each Function?

In-depth understanding of the RANK() and ROW_NUMBER() functions in SQL

The RANK() and ROW_NUMBER() functions in SQL are often confusing, and this article aims to clarify the differences between the two.

First of all, it should be noted that if there are no duplicate values ​​in the partition, the SQL query results of these two functions will be exactly the same. However, when duplicate values ​​appear, the difference becomes apparent.

Duplicate values ​​and certainty

ROW_NUMBER() function assigns a unique incremental value to each row within a partition, even if the rows have the same value on the sort column. This means that ROW_NUMBER() is non-deterministic and the value it assigns may change arbitrarily on each query.

In contrast, RANK() and DENSE_RANK() are both deterministic within a partition. Multiple rows will be assigned the same rank value if they have the same value on both the sorting column and the partitioning column.

Example

Consider the following example:

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

In this example, when sorting by ID within the same partition (StyleID), the first three rows are duplicated. Here's how each function handles these duplicate values:

• ROW_NUMBER() assigns increasing values ​​(1, 2, 3) and ignores duplicate values.
• RANK() assigns all three duplicate rows the same rank value (1), then jumps to 4 for the next different value.
• DENSE_RANK() also assigns duplicate rows the same rank value (1), but increments to 2 for the next different value.

Conclusion

RANK() and DENSE_RANK() maintain consistent rankings within partitions even in the presence of duplicate values. However, ROW_NUMBER() arbitrarily assigns increasing values, which can lead to unpredictable results. Understanding these differences will help you choose the appropriate function based on your specific SQL needs.

The above is the detailed content of SQL RANK() vs. ROW_NUMBER(): When Should You Use Each Function?. For more information, please follow other related articles on the PHP Chinese website!