SQL RANK(): Rank Rows With Ties (and How It Differs From ROW_NUMBER)

RANK() assigns a position to each row based on an order, like the results table of a race. Rows with equal values share the same rank, and the next rank skips ahead by the number of tied rows.

The example table#

scores (Maths)

Syntax#

RANK() OVER (
  [PARTITION BY column]
  ORDER BY column
)

Ranking with ties#

SELECT student, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores
WHERE subject = 'Maths'
ORDER BY rnk, student;

student | score | rnk
--------+-------+----
Asha    | 92    | 1
Meera   | 92    | 1
Priya   | 85    | 3
Ravi    | 85    | 3
John    | 78    | 5

Asha and Meera tie for first, so both get rank 1. Because two people are ahead of them, Priya and Ravi are rank 3, not 2. That gap is what distinguishes RANK from DENSE_RANK.

ROW_NUMBER vs RANK vs DENSE_RANK#

All three side by side on the same data:

SELECT student, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, student) AS row_num,
       RANK()       OVER (ORDER BY score DESC)          AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC)          AS dense
FROM scores
WHERE subject = 'Maths'
ORDER BY score DESC, student;

student | score | row_num | rnk | dense
--------+-------+---------+-----+------
Asha    | 92    | 1       | 1   | 1
Meera   | 92    | 2       | 1   | 1
Priya   | 85    | 3       | 3   | 2
Ravi    | 85    | 4       | 3   | 2
John    | 78    | 5       | 5   | 3

Choose RANK when positions should reflect how many rows are ahead, as in sports results: if two people tie for first, nobody is second.

Ranking within groups#

PARTITION BY ranks separately within each subject:

SELECT subject, student, score,
       RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rnk
FROM scores
ORDER BY subject, rnk, student;

subject | student | score | rnk
--------+---------+-------+----
Maths   | Asha    | 92    | 1
Maths   | Meera   | 92    | 1
Maths   | Priya   | 85    | 3
Maths   | Ravi    | 85    | 3
Maths   | John    | 78    | 5
Science | Ravi    | 95    | 1
Science | Asha    | 88    | 2
Science | John    | 88    | 2
Science | Meera   | 80    | 4
Science | Priya   | 70    | 5

Top N including ties#

Filtering rnk <= N returns everyone tied at the cut-off, which ROW_NUMBER cannot do:

SELECT student, score
FROM (
  SELECT student, score,
         RANK() OVER (ORDER BY score DESC) AS rnk
  FROM scores
  WHERE subject = 'Maths'
) t
WHERE rnk <= 2
ORDER BY score DESC, student;

student | score
--------+------
Asha    | 92
Meera   | 92

"Top 2" returns both students tied for first. With ROW_NUMBER, one of them would be arbitrarily left out, and with rnk <= 3 the two students on 85 would join them.

Common mistakes#

Practice#

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

name  | salary | rnk
------+--------+----
Meera | 81000  | 1
Ravi  | 78000  | 2
Priya | 61000  | 3
Asha  | 52000  | 4
John  | 46000  | 5

Key takeaway#