A ranking query contains duplicate salaries. How will ROW_NUMBER(), RANK(), and DENSE_RANK() assign values differently?
Interview preparation resource from Gate Smashers.
ROW_NUMBER gives every row a unique sequence; RANK gives ties the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps.
| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 80000 | 2 | 2 | 2 |
| 80000 | 3 | 2 | 2 |
| 70000 | 4 | 4 | 3 |
SELECT employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
RANK() OVER (ORDER BY salary DESC) AS rank_no,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_no
FROM Employees;Direct answer
ROW_NUMBER assigns a unique sequential number to each row. RANK assigns the same rank to tied values but leaves gaps after ties. DENSE_RANK assigns the same rank to tied values and does not leave gaps.
Concrete example
For the salaries 90000, 80000, 80000 and 70000 the functions produce different sequences as shown in the table below.
- ROW_NUMBER: 1, 2, 3, 4 (unique sequence for every row)
- RANK: 1, 2, 2, 4 (ties share rank; gap after the tie)
- DENSE_RANK: 1, 2, 2, 3 (ties share rank; no gap)
Practical notes
When you need repeatable ordering with ROW_NUMBER (so ties always resolve the same way), add a deterministic tie-breaker to ORDER BY (for example, ORDER BY salary DESC, employee_id ASC). Use DENSE_RANK when you need the nth distinct salary (e.g., find the 2nd highest distinct salary).
SQL example
This query shows all three ranking functions over salaries ordered descending.