SQL RANK() versus ROW_NUMBER()

by

You will only see the difference if you have ties within a partition for a particular ordering value.

RANK and DENSE_RANK are deterministic in this case, all rows with the same value for both the ordering and partitioning columns will end up with an equal result, whereas ROW_NUMBER will arbitrarily (non deterministically) assign an incrementing result to the tied rows.

Example: (All rows have the same StyleID so are in the same partition and within that partition the first 3 rows are tied when ordered by ID)

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

Returns

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

You can see that for the three identical rows the ROW_NUMBER increments, the RANK value remains the same then it leaps to 4. DENSE_RANK also assigns the same rank to all three rows but then the next distinct value is assigned a value of 2.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. SQL Server dynamic PIVOT query?
  3. SQL Server – Best way to get identity of inserted row?
  4. SQL Server Process Queue Race Condition
  5. Is the NOLOCK (Sql Server hint) bad practice?
  6. Creating an index on a table variable
  7. SQL query to split column data into rows
  8. What is the difference between using a cross join and putting a comma between the two tables?
  9. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  10. Pivot using SQL Server 2000
  11. How to drop SQL default constraint without knowing its name?
  12. Equivalent of Oracle’s RowID in SQL Server
  13. How to update and order by using ms sql
  14. Dynamic update statement with variable column names
  15. How to select only the first rows for each unique value of a column?
  16. How to store image in SQL Server database tables column [duplicate]
  17. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  18. SQL “between” not inclusive
  19. Can I set ignore_dup_key on for a primary key?
  20. Unordered results in SQL
  21. Using PIVOT in SQL Server 2008
  22. SQL Server replace, remove all after certain character
  23. Inserting rows into a table with one IDENTITY column only [duplicate]
  24. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  25. When using GETDATE() in many places, is it better to use a variable?
  26. How do I compare 2 rows from the same table (SQL Server)?
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment