RANK() and DENSE_RANK() — Handling Ties in Rankings

Posted Jul 15, 2026

By Rahul Chandna

10 min read

title: “RANK() and DENSE_RANK()” part: 3 topic_number: 5 slug: “rank” difficulty: “Advanced” prerequisites: “row-number” —

RANK() and DENSE_RANK() — Handling Ties in Rankings

What Is It?

RANK() and DENSE_RANK() are window functions (like ROW_NUMBER()) that handle ties differently:

ROW_NUMBER(): Always unique (1, 2, 3, 4…)
RANK(): Same rank for ties, skips numbers (1, 2, 2, 4, 5…)
DENSE_RANK(): Same rank for ties, no gaps (1, 2, 2, 3, 4…)

Real-world analogy: Olympic medals — two athletes tie for silver (both get rank 2), but the next athlete gets bronze (rank 3 with DENSE_RANK, rank 4 with RANK).

See the Difference Immediately

Here is what all three produce when there are ties — memorise this table before reading further:

Score	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
90	2	2	2
90	3	2	2
80	4	4 ← gap	3 ← no gap
70	5	5	4

ROW_NUMBER — never repeats, always unique, tie-breaking is arbitrary
RANK — ties share a number, then the count of rows above determines the next rank (two people tied at 2 → next rank is 4, not 3)
DENSE_RANK — ties share a number, next rank always increments by 1

Quick rule: use DENSE_RANK for “top N salary levels”; use RANK for “true position”; use ROW_NUMBER when you need a unique number per row.

Syntax (Same as ROW_NUMBER)

        
      
RANK() OVER (
    [PARTITION BY column(s)]
    ORDER BY column(s) [ASC|DESC]
)

DENSE_RANK() OVER (
    [PARTITION BY column(s)]
    ORDER BY column(s) [ASC|DESC]
)

Side-by-Side Comparison (With Ties)

Use a WITH clause to create a small dataset with a deliberate tie — no need to UPDATE real data:

        
      
WITH scores AS (
    SELECT 'Alice'  AS name, 95000 AS salary UNION ALL
    SELECT 'Frank',           93000           UNION ALL
    SELECT 'Henry',           92000           UNION ALL
    SELECT 'Bob',             82000           UNION ALL
    SELECT 'Carol',           82000           UNION ALL   -- TIE with Bob
    SELECT 'Eve',             78000
)
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 scores
ORDER BY salary DESC, name;

Expected output:

name	salary	row_num	rnk	dense_rnk
Alice	95000	1	1	1
Frank	93000	2	2	2
Henry	92000	3	3	3
Bob	82000	4	4	4
Carol	82000	5	4	4
Eve	78000	6	6 ← gap	5 ← no gap

Key differences:

ROW_NUMBER: Bob=4, Carol=5 (always unique, tie broken by name)
RANK: Both get 4, Eve gets 6 (skips 5 — two people occupied positions 4 and 5)
DENSE_RANK: Both get 4, Eve gets 5 (no gap, just “next distinct level”)

Going Deeper

Top 3 Salaries (Including Ties)

        
      
WITH ranked AS (
    SELECT 
        first_name,
        last_name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
    FROM employees
)
SELECT *
FROM ranked
WHERE dense_rank <= 3
ORDER BY dense_rank, salary DESC, last_name;

Expected output:

first_name	last_name	salary	dense_rank
Sam	Clark	95000.00	1
Frank	Miller	93000.00	2
Henry	Moore	92000.00	3

Top 3 unique salary levels (if there were ties, would include all tied employees).

Department Rankings

        
      
SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dept_rank,
    CASE 
        WHEN RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) = 1 THEN 'Gold'
        WHEN RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) = 2 THEN 'Silver'
        WHEN RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) = 3 THEN 'Bronze'
        ELSE ''
    END AS medal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, dept_rank;

Expected output:

first_name	last_name	salary	department_name	dept_rank	medal
Sam	Clark	95000.00	Engineering	1	Gold
Paul	Garcia	87000.00	Engineering	2	Silver
Alice	Johnson	75000.00	Engineering	3	Bronze
Henry	Moore	92000.00	Finance	1	Gold
David	Brown	66000.00	Finance	2	Silver
…	…	…	…	…	…

Top performers per department with medals!

Percentile Rankings

        
      
SELECT 
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    ROUND(PERCENT_RANK() OVER (ORDER BY salary DESC) * 100, 1) AS percentile
FROM employees
ORDER BY salary DESC;

Expected output:

first_name	salary	salary_rank	percentile
Sam	95000.00	1	0.0
Frank	93000.00	2	5.3
Henry	92000.00	3	10.5
Paul	87000.00	4	15.8
Bob	82000.00	5	21.1
…	…	…	…

Percentile rank (Sam is in top 0%, Frank in top 5.3%, etc.).

Finding Ties

        
      
SELECT 
    salary,
    COUNT(*) AS employee_count,
    GROUP_CONCAT(first_name ORDER BY first_name) AS employees
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
ORDER BY salary DESC;

Expected output:

salary	employee_count	employees
82000.00	2	Bob,Carol
70000.00	2	Jack,Noah

Salaries with ties (multiple employees).

Pause and Predict: When would you use RANK() vs DENSE_RANK()?

Answer

**Use RANK() when:** - You want rankings to reflect the "true" position (e.g., "4th place" after two people tied for 2nd) - Gap numbers matter conceptually (Olympic-style ranking) **Use DENSE_RANK() when:** - You need continuous rankings without gaps - Counting "distinct levels" (top 5 salary levels, including all ties) - Creating categories/buckets **Use ROW_NUMBER() when:** - You need absolutely unique identifiers - Breaking ties arbitrarily is acceptable - Pagination or strict ordering required

Watch Out — Common Mistakes

Mistake #1: Expecting ROW_NUMBER Behavior with RANK

        
      
--  WRONG ASSUMPTION
WITH ranked AS (
    SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk = 5;
-- Might return 0 rows if there are ties above rank 5!

Example: If 3 people tie for rank 2, next person is rank 5 (skipped 3 and 4). Your query looking for rank 5 might not find what you expect.

Mistake #2: Using DENSE_RANK for Pagination

        
      
--  BAD FOR PAGINATION
SELECT 
    *,
    DENSE_RANK() OVER (ORDER BY employee_id) AS page
FROM employees
WHERE page BETWEEN 10 AND 20;  -- Won't work as expected with gaps!

Fix: Use ROW_NUMBER for pagination (guarantees sequential numbers).

Mistake #3: Forgetting ORDER BY Matters

        
      
-- Different ORDER BY = different rankings!
RANK() OVER (ORDER BY salary DESC)  -- Rank by highest salary
RANK() OVER (ORDER BY hire_date)    -- Rank by seniority

Always consider what you’re ranking BY.

Edge Case Spotlight

All Ties (Everyone Same Value)

        
      
-- If everyone has same salary
UPDATE employees SET salary = 75000;

SELECT 
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

Result: Everyone gets rank 1 for both RANK() and DENSE_RANK().

Combining with Aggregates

        
      
SELECT 
    department_id,
    AVG(salary) AS avg_salary,
    RANK() OVER (ORDER BY AVG(salary) DESC) AS dept_rank
FROM employees
GROUP BY department_id;

Expected output:

department_id	avg_salary	dept_rank
2	87500.00	1
1	85666.67	2
4	79000.00	3
…	…	…

Rank departments by average salary.

Try This

Exercise 1 (Guided)

Find all employees tied for the highest salary in their department. Show first_name, last_name, department_name, salary, and dept_rank.

Hint

Use RANK() OVER (PARTITION BY department_id ORDER BY salary DESC), filter WHERE dept_rank = 1.

Exercise 2 (Independent)

Create a “performance quintile” report: divide employees into 5 groups based on salary (top 20%, next 20%, etc.). Show first_name, salary, and quintile (1-5).

Hint

Use NTILE(5) OVER (ORDER BY salary DESC) — NTILE divides into N roughly equal buckets.

Exercise 3 (Challenge)

Find “salary outliers”: employees whose salary rank within their department is very different from their overall company rank (difference > 3). Show name, salary, dept_rank, overall_rank, and rank_difference.

Hint

Use two RANK() windows (one with PARTITION BY dept, one without), calculate ABS(dept_rank - overall_rank), filter > 3.

Answer Key

Exercise 1 Answer

```sql WITH ranked_employees AS ( SELECT e.first_name, e.last_name, e.salary, d.department_name, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dept_rank FROM employees e JOIN departments d ON e.department_id = d.department_id ) SELECT * FROM ranked_employees WHERE dept_rank = 1 ORDER BY department_name; ``` **Expected output:** | first_name | last_name | salary | department_name | dept_rank | |------------|-----------|---------|-----------------|-----------| | Sam | Clark | 95000.00 | Engineering | 1 | | Henry | Moore | 92000.00 | Finance | 1 | | Ivy | Anderson | 74000.00 | Human Resources | 1 | | George | Jones | 71000.00 | Legal | 1 | | ... | ... | ... | ... | ... | Top earners per department (includes all ties).

Exercise 2 Answer

```sql SELECT first_name, last_name, salary, NTILE(5) OVER (ORDER BY salary DESC) AS quintile, CASE NTILE(5) OVER (ORDER BY salary DESC) WHEN 1 THEN 'Top 20%' WHEN 2 THEN '60-80%' WHEN 3 THEN '40-60%' WHEN 4 THEN '20-40%' WHEN 5 THEN 'Bottom 20%' END AS performance_group FROM employees ORDER BY salary DESC; ``` **Expected output:** | first_name | last_name | salary | quintile | performance_group | |------------|-----------|---------|----------|-------------------| | Sam | Clark | 95000.00 | 1 | Top 20% | | Frank | Miller | 93000.00 | 1 | Top 20% | | Henry | Moore | 92000.00 | 1 | Top 20% | | Paul | Garcia | 87000.00 | 1 | Top 20% | | Bob | Smith | 82000.00 | 2 | 60-80% | | ... | ... | ... | ... | ... | Employees divided into performance quintiles. **Note:** NTILE(5) creates 5 buckets of roughly equal size.

Exercise 3 Answer

```sql WITH rankings AS ( SELECT e.first_name, e.last_name, e.salary, d.department_name, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dept_rank, RANK() OVER (ORDER BY e.salary DESC) AS overall_rank FROM employees e JOIN departments d ON e.department_id = d.department_id ) SELECT first_name, last_name, salary, department_name, dept_rank, overall_rank, ABS(dept_rank - overall_rank) AS rank_difference FROM rankings WHERE ABS(dept_rank - overall_rank) > 3 ORDER BY rank_difference DESC; ``` **Expected output:** | first_name | last_name | salary | department_name | dept_rank | overall_rank | rank_difference | |------------|-----------|---------|-----------------|-----------|--------------|-----------------| | Alice | Johnson | 75000.00 | Engineering | 3 | 7 | 4 | | David | Brown | 66000.00 | Finance | 2 | 12 | 10 | Employees whose department rank differs significantly from overall rank. **Interpretation:** David is #2 in his department but only #12 overall → his department has lower salaries.

Quick Recap

• RANK() assigns same rank to ties, skips numbers (1, 2, 2, 4…)
• DENSE_RANK() assigns same rank to ties, no gaps (1, 2, 2, 3…)
• ROW_NUMBER() always unique (1, 2, 3, 4…)
• Use RANK for “true position” rankings
• Use DENSE_RANK for “top N levels” including all ties
• Use ROW_NUMBER for pagination or unique IDs
• NTILE(n) divides into n equal buckets
• PERCENT_RANK() calculates percentile (0.0 to 1.0)

Up Next

Next topic: Transactions (BEGIN, COMMIT, ROLLBACK) → part3_06_transactions.md

Type ‘next’ when ready to continue!

Graduate Training, MySQL

Graduate Training

This post is licensed under CC BY 4.0 by the author.

RANK() and DENSE_RANK() — Handling Ties in Rankings

What Is It?

See the Difference Immediately

Syntax (Same as ROW_NUMBER)

Side-by-Side Comparison (With Ties)

Going Deeper

Top 3 Salaries (Including Ties)

Department Rankings

Percentile Rankings

Finding Ties

Watch Out — Common Mistakes

Mistake #1: Expecting ROW_NUMBER Behavior with RANK

Mistake #2: Using DENSE_RANK for Pagination

Mistake #3: Forgetting ORDER BY Matters

Edge Case Spotlight

All Ties (Everyone Same Value)

Combining with Aggregates

Try This

Exercise 1 (Guided)

Exercise 2 (Independent)

Exercise 3 (Challenge)

Answer Key

Quick Recap

Up Next

Trending Tags