ROW_NUMBER, RANK, and DENSE_RANK
Three ranking functions with critical differences in how they handle ties.
- ROW_NUMBER β Unique sequential integers, no ties
- RANK β Ties share a rank, gaps follow tied groups
- DENSE_RANK β Ties share a rank, no gaps in numbering Choosing the right function depends on whether gaps in ranking matter.
The Three Ranking Functions
DfROW_NUMBER()
Assigns a unique sequential integer to each row within a partition. Ties are broken arbitrarily β no two rows share the same number.
DfRANK()
Assigns the same rank to tied values, then leaves gaps after the tie. If two rows tie for rank 2, the next row is rank 4.
DfDENSE_RANK()
Like RANK, but leaves no gaps. If two rows tie for rank 2, the next row is rank 3.
Visual Comparison
| Row | Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| A | 90000 | 1 | 1 | 1 |
| B | 85000 | 2 | 2 | 2 |
| C | 85000 | 3 | 2 | 2 |
| D | 80000 | 4 | 4 | 3 |
| E | 75000 | 5 | 5 | 4 |
Notice how ROW_NUMBER assigns unique numbers even to ties, RANK skips to 4 after the tie at 2, and DENSE_RANK stays sequential.
Core Syntax
-- All three ranking functions side by side
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_val,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_val
FROM employees;
When ranking employees by salary, use ROW_NUMBER when you need a unique ID per row, DENSE_RANK when you want clean "1st, 2nd, 3rd" labels, and RANK when gaps in ranking convey meaning (e.g., competition standings).
ROW_NUMBER β Unique Ordering
DfROW_NUMBER Use Case
Best for assigning unique identifiers within groups β pagination, deduplication, or selecting the first record per group.
-- Select the most recent order per customer
WITH ranked_orders AS (
SELECT
customer_id,
order_id,
order_date,
order_amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
)
SELECT customer_id, order_id, order_date, order_amount
FROM ranked_orders
WHERE rn = 1;
-- Deduplicate: keep only the latest record per student
WITH deduplicated AS (
SELECT
student_id,
course_name,
grade,
submission_date,
ROW_NUMBER() OVER (
PARTITION BY student_id, course_name
ORDER BY submission_date DESC
) AS rn
FROM student_submissions
)
SELECT student_id, course_name, grade, submission_date
FROM deduplicated
WHERE rn = 1;
RANK β Gaps After Ties
-- Competition results with RANK (ties share rank, gaps follow)
SELECT
contestant_name,
score,
RANK() OVER (ORDER BY score DESC) AS competition_rank
FROM contest_results
ORDER BY competition_rank;
-- Output:
-- Alice | 98 | 1
-- Bob | 95 | 2
-- Charlie | 95 | 2
-- Diana | 92 | 4 <-- Gap after the tie at rank 2
DENSE_RANK β No Gaps
-- Grade assignment with DENSE_RANK (no gaps after ties)
SELECT
student_name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS grade_rank,
CASE
WHEN DENSE_RANK() OVER (ORDER BY score DESC) <= 3 THEN 'Honors'
WHEN DENSE_RANK() OVER (ORDER BY score DESC) <= 7 THEN 'Pass'
ELSE 'Needs Improvement'
END AS designation
FROM student_scores;
Practical Scenario: Top N Per Group
DfTop N Per Group
A common SQL pattern where you need the N highest or lowest values within each category. Ranking functions combined with a WHERE filter make this efficient.
-- Top 3 earners in each department
WITH department_ranks AS (
SELECT
employee_name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees
)
SELECT employee_name, department, salary, dept_rank
FROM department_ranks
WHERE dept_rank <= 3
ORDER BY department, dept_rank;
-- Bottom 2 performers per department
WITH bottom_performers AS (
SELECT
employee_name,
department,
performance_score,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY performance_score ASC
) AS bottom_rank
FROM employees
WHERE performance_score IS NOT NULL
)
SELECT employee_name, department, performance_score
FROM bottom_performers
WHERE bottom_rank <= 2;
When to Use Each
| Scenario | Recommended | Why |
|---|---|---|
| Pagination | ROW_NUMBER | Need unique sequential IDs |
| Deduplication | ROW_NUMBER | Pick exactly one row per group |
| Competition ranking | RANK | Ties should be equal, gaps expected |
| Leaderboard display | DENSE_RANK | Clean numbering without gaps |
| Percentile assignment | NTILE | Split into N equal groups |
Using ROW_NUMBER when ties exist will arbitrarily break ties based on the database's internal ordering. If deterministic results are required, add a tiebreaker column to the ORDER BY clause.
Combining with Other Window Functions
-- Full salary analysis with ranking and comparison
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_val,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_val,
LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS gap_to_prev
FROM employees
ORDER BY department, row_num;
Key Takeaways
ROW_NUMBERassigns unique sequential integers β no ties, no gapsRANKties share the same rank, then skips ranks after a tie (gaps exist)DENSE_RANKties share the same rank, no gaps follow (clean numbering)- Use ROW_NUMBER for deduplication and pagination where unique row IDs are needed
- Always add a tiebreaker column to ORDER BY when deterministic ranking is required