RANK()
SQL · Window Functions
Theory
RANK() numbers rows by the window ORDER BY, but rows with equal values share the same rank, and the numbers after a tie are skipped. If Asha and Priya both earn 60000 and only Meera earns more, they are both rank 2, and the next person is rank 4, not 3. There is no rank 3 because two people occupy positions 2 and 3.
This is the 'sports ranking' style: if two runners tie for second, nobody is third. The rank value always equals 1 plus the number of rows that come strictly before this row. With PARTITION BY, ranking restarts in each group. Filtering WHERE rnk <= 3 can return more than 3 rows when there are ties, which is often exactly what you want for a fair 'top 3'. The query's ORDER BY direction matters: ORDER BY salary DESC ranks the highest salary as 1.
Why we use it
When ties are genuine, giving tied rows different numbers (as ROW_NUMBER does) is unfair and misleading. RANK treats equal values equally, and the gaps make it clear how many rows are ahead of you.
When to use it
Use RANK for leaderboards, exam results or sales contests where ties should share a place and gaps are expected. Use DENSE_RANK if you need consecutive numbers (like 'second highest salary'), or ROW_NUMBER if you need exactly one row per position.
How it works
- Partition is sorted by the window ORDER BY
- First row gets rank 1
- Same value as previous row? Yes: Same rank. No: Rank = row position (1 + rows before).
- Gaps appear after every tie
- Rank column added to each row
Syntax
RANK() OVER (ORDER BY col)Example
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;Key points
- Ties share a rank; the following rank is skipped (1, 2, 2, 4)
- Rank = 1 + number of rows strictly ahead of this row
- Useful for leaderboards where ties matter
- WHERE rnk <= 3 can return more than 3 rows when tied
- Restarts per partition when PARTITION BY is used