Skip to content
Quantoros

What is the difference between ROWNUMBER, RANK, and DENSERANK?

SQLLevel: IntermediateWindow Functions

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

Hints
  • Which rows belong to the partition and frame for each current row?
  • Check ordering, ties, frame boundaries and the difference from GROUP BY.

Answer

They all assign a number to each row within a window, ordered by some column. They differ in how they handle ties:

  • ROW_NUMBER(): assigns a unique sequential number regardless of ties (ties get arbitrary but distinct numbers)
  • RANK(): tied rows get the same rank, then the next rank skips (1, 2, 2, 4)
  • DENSE_RANK(): tied rows get the same rank, but no gaps (1, 2, 2, 3)
SELECT
    name,
    salary,
    ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_num,
    RANK()        OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

/*
name     | salary | row_num | rnk | dense_rnk
---------|--------|---------|-----|----------
Alice    | 100    | 1       | 1   | 1
Bob      | 90     | 2       | 2   | 2
Carol    | 90     | 3       | 2   | 2
Dave     | 80     | 4       | 4   | 3
*/

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions