SQL 56 🛢️ Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK - KandZ

A ranking function assigns a number to each row based on its position in an ordered set. The three functions—ROW_NUMBER, RANK, and DENSE_RANK—look similar and are often confused, but they handle ties differently. The difference matters in practice: a leaderboard where ties should share a rank needs RANK, a paginated list where every row needs a unique position needs ROW_NUMBER, and a report where the ranks should not skip after a tie needs DENSE_RANK. Choosing the wrong one produces off-by-one errors that are hard to debug.

Key point: ROW_NUMBER() assigns a unique sequential number to each row, breaking ties arbitrarily. RANK() assigns the same rank to tied rows and skips the next rank. DENSE_RANK() assigns the same rank to tied rows and does not skip. All three require an ORDER BY inside the OVER clause, and all three respect PARTITION BY when it is present.

Why ranking functions exist

The position problem. A query that sorts the rows produces an order but not a position. The ORDER BY clause determines which row comes first, but it does not give the row a number. The ranking functions produce the number. The ROW_NUMBER is the position, the RANK is the competition rank, and the DENSE_RANK is the gap-free rank.

The tie problem. Two rows with the same value in the ORDER BY column are tied. The ROW_NUMBER breaks the tie arbitrarily, and the result depends on the query plan. The RANK gives both rows the same rank and skips the next, the same as the Olympic medal ranking. The DENSE_RANK gives both rows the same rank and does not skip. The choice depends on what the ties should mean.

The top-N problem. “The top three products per category” needs a ranking function inside a subquery and a filter on the rank in the outer query. The ROW_NUMBER is the choice when the three must be distinct. The RANK is the choice when the ties should be included. The DENSE_RANK is the choice when the number of distinct ranks matters.

The pagination problem. “The rows 21 through 40 in the sorted order” is the ROW_NUMBER with the BETWEEN filter. The RANK and the DENSE_RANK do not have the unique numbers that the pagination needs.

The partition problem. The ranking within each group is the PARTITION BY. “The top three products per category” is the ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC). The partition resets the numbering for each group.

The determinism problem. The ROW_NUMBER with the ties is non-deterministic. The same query on the same data can produce different numbers on different runs. The fix is the tiebreaker in the ORDER BY: the ORDER BY price DESC, id ASC makes the order deterministic and the ROW_NUMBER stable.

a. ROW_NUMBER

The ROW_NUMBER() assigns a unique sequential number to each row in the order.

SELECT
    product_id,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

The rn starts at 1 for the highest price in each category and increments by 1 for each row. The ties in the price get different numbers, and the order among the ties is arbitrary.

The tiebreaker makes the order deterministic.

SELECT
    product_id,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, product_id) AS rn
FROM products;

The product_id is the tiebreaker. The tied rows are ordered by the product_id, and the rn is stable across the runs.

The top N per group uses the ROW_NUMBER in the subquery.

SELECT * FROM (
    SELECT
        product_id,
        category,
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) AS ranked
WHERE rn <= 3;

The inner query assigns the rn, and the outer query filters the top three per category. The three are distinct even when the prices tie.

The pagination uses the ROW_NUMBER with the range.

SELECT * FROM (
    SELECT
        product_id,
        price,
        ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
    FROM products
) AS ranked
WHERE rn BETWEEN 21 AND 40;

The rows 21 through 40 in the price order. The rn is the page position.

b. RANK and DENSE_RANK

The RANK() assigns the same rank to the tied rows and skips the next rank.

SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

For the salaries [100, 90, 90, 80], the ranks are 1, 2, 2, 4. The two 90s share the rank 2, and the rank 3 is skipped. The next salary (80) gets the rank 4.

The DENSE_RANK() assigns the same rank to the tied rows and does not skip.

SELECT
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

For the salaries [100, 90, 90, 80], the dense ranks are 1, 2, 2, 3. The two 90s share the rank 2, and the next salary (80) gets the rank 3. There is no gap.

The comparison:

Salary	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
90	2	2	2
90	3	2	2
80	4	4	3

The ROW_NUMBER is the unique position. The RANK is the competition rank with the gaps. The DENSE_RANK is the gap-free rank.

The RANK with the partition.

SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

The rank resets for each department. The highest salary in each department gets the rank 1.

The DENSE_RANK for the “how many distinct values” question.

SELECT
    department,
    MAX(dense_rank) AS distinct_salaries
FROM (
    SELECT
        department,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) AS dense_rank
    FROM employees
) AS ranked
GROUP BY department;

The maximum dense rank is the number of the distinct salaries in the department. The RANK would give the number of the rows plus the gaps, and the ROW_NUMBER would give the number of the rows.

c. The use cases

The three functions have the distinct use cases.

Use Case	Function
The unique position	`ROW_NUMBER`
The top N distinct	`ROW_NUMBER`
The top N with the ties	`RANK`
The leaderboard with the shared ranks	`RANK`
The gap-free rank	`DENSE_RANK`
The count of the distinct values	`DENSE_RANK`
The pagination	`ROW_NUMBER`
The “nth highest”	`DENSE_RANK`

The “nth highest salary” is the classic DENSE_RANK question.

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked
WHERE rnk = 2;

The result is all the employees with the second-highest salary. The DENSE_RANK handles the ties correctly: the second distinct salary, not the second row.

The ROW_NUMBER for the “nth row”.

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) AS ranked
WHERE rn = 2;

The result is the single employee in the second position. The ties are broken arbitrarily, and the result is one row.

The difference: the DENSE_RANK returns all the employees with the second distinct salary, and the ROW_NUMBER returns the single employee in the second position.

The RANK for the “top N with the ties”.

SELECT * FROM (
    SELECT
        employee_id,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked
WHERE rnk <= 3;

The result is the top three ranks, including all the ties. The ROW_NUMBER with the rn <= 3 would return the three rows and exclude the ties beyond the third.

The ROW_NUMBER for the deduplication.

SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) AS ranked
WHERE rn = 1;

The result is the most recent row per email. The ROW_NUMBER with the partition and the filter is the standard deduplication pattern.

The DENSE_RANK for the “how many distinct”.

SELECT COUNT(DISTINCT salary) FROM employees;
-- Or:
SELECT MAX(dense_rank) FROM (
    SELECT DENSE_RANK() OVER (ORDER BY salary) AS dense_rank FROM employees
) AS ranked;

The two are equivalent. The COUNT(DISTINCT) is simpler, and the DENSE_RANK is the alternative when the distinct count is needed in a window context.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE employees (
    employee_id INT,
    department VARCHAR(20),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
    (1, 'Eng', 95000),
    (2, 'Eng', 85000),
    (3, 'Eng', 85000),
    (4, 'Eng', 75000),
    (5, 'Sales', 75000),
    (6, 'Sales', 65000);

-- ============================================
-- PART 2: ROW_NUMBER
-- ============================================
SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
-- Eng: 1, 2, 3, 4
-- Sales: 1, 2

-- ============================================
-- PART 3: RANK
-- ============================================
SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- 95000: 1
-- 85000: 2, 2
-- 75000: 4, 4
-- 65000: 6

-- ============================================
-- PART 4: DENSE_RANK
-- ============================================
SELECT
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- 95000: 1
-- 85000: 2, 2
-- 75000: 3, 3
-- 65000: 4

-- ============================================
-- PART 5: COMPARISON
-- ============================================
SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK() OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

-- ============================================
-- PART 6: TOP 3 PER DEPARTMENT (ROW_NUMBER)
-- ============================================
SELECT * FROM (
    SELECT
        employee_id,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) AS ranked
WHERE rn <= 3;

-- ============================================
-- PART 7: TOP 3 WITH THE TIES (RANK)
-- ============================================
SELECT * FROM (
    SELECT
        employee_id,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked
WHERE rnk <= 3;

-- ============================================
-- PART 8: SECOND HIGHEST (DENSE_RANK)
-- ============================================
SELECT * FROM (
    SELECT
        employee_id,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked
WHERE rnk = 2;

-- ============================================
-- PART 9: DEDUPLICATION (ROW_NUMBER)
-- ============================================
SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) AS ranked
WHERE rn = 1;

-- ============================================
-- PART 10: DISTINCT COUNT (DENSE_RANK)
-- ============================================
SELECT MAX(dense_rank) AS distinct_salaries
FROM (
    SELECT DENSE_RANK() OVER (ORDER BY salary) AS dense_rank
    FROM employees
) AS ranked;

The ten parts covered the sample data, the ROW_NUMBER, the RANK, the DENSE_RANK, the comparison, the top 3 per department with the ROW_NUMBER, the top 3 with the ties with the RANK, the second highest with the DENSE_RANK, the deduplication with the ROW_NUMBER, and the distinct count with the DENSE_RANK.

Quick Reference

The Three Functions

Function	Ties	Gaps
`ROW_NUMBER()`	Unique numbers	None
`RANK()`	Same rank	Yes
`DENSE_RANK()`	Same rank	No

The Example

Salary	`ROW_NUMBER`	`RANK`	`DENSE_RANK`
100	1	1	1
90	2	2	2
90	3	2	2
80	4	4	3

Use Cases

Use Case	Function
Unique position	`ROW_NUMBER`
Top N distinct	`ROW_NUMBER`
Top N with ties	`RANK`
Leaderboard	`RANK`
Gap-free rank	`DENSE_RANK`
Distinct count	`DENSE_RANK`
Pagination	`ROW_NUMBER`
Nth highest	`DENSE_RANK`
Deduplication	`ROW_NUMBER`

The OVER Clause

Clause	Purpose
`PARTITION BY`	Reset the numbering
`ORDER BY`	Determine the rank order
Tiebreaker	Make the order deterministic

Best Practices

✅ Do This:

-- Use ROW_NUMBER for the unique position
ROW_NUMBER() OVER (ORDER BY price DESC)                        -- ✅

-- Use RANK for the competition rank
RANK() OVER (ORDER BY score DESC)                              -- ✅

-- Use DENSE_RANK for the gap-free rank
DENSE_RANK() OVER (ORDER BY salary DESC)                       -- ✅

-- Add the tiebreaker for the deterministic ROW_NUMBER
ROW_NUMBER() OVER (ORDER BY price DESC, id ASC)                -- ✅

-- Use the partition for the per-group ranking
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC)  -- ✅

-- Wrap in the subquery to filter on the rank
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t
) AS ranked WHERE rn <= 3;                                     -- ✅

❌ Don’t Do This:

-- Don't use ROW_NUMBER when the ties should share
ROW_NUMBER() OVER (ORDER BY score DESC)  -- ties get different -- ⚠️

-- Don't use RANK when the gaps are unwanted
RANK() OVER (ORDER BY level)  -- 1, 2, 2, 4                    -- ⚠️

-- Don't forget the ORDER BY
ROW_NUMBER() OVER ()  -- arbitrary order, no meaning           -- ❌

-- Don't use the window function in WHERE
WHERE ROW_NUMBER() OVER (...) = 1  -- error                     -- ❌

-- Don't use ROW_NUMBER without the tiebreaker for the stable result
ROW_NUMBER() OVER (ORDER BY price DESC)  -- ties arbitrary     -- ⚠️

-- Don't use the ranking function when the DISTINCT fits
DENSE_RANK() OVER (ORDER BY salary)  -- for the count          -- ⚠️

Common Pitfalls

Pitfall	Why It Happens	Fix
Non-deterministic ROW_NUMBER	No tiebreaker	Add the column
Gaps in RANK	The ties skipped	Use `DENSE_RANK`
Window function in `WHERE`	Not allowed	Wrap in the subquery
Missing `ORDER BY`	No order	Add the `ORDER BY`
Wrong partition	The group reset missing	Add the `PARTITION BY`
Ties excluded from the top N	The `ROW_NUMBER` filter	Use `RANK`

Real-World Examples

1. Top 3 per Category

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) AS ranked WHERE rn <= 3;

2. Leaderboard

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM players;

3. Second Highest

SELECT * FROM (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) AS ranked WHERE rnk = 2;

4. Deduplication

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) AS ranked WHERE rn = 1;

5. Pagination

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
    FROM products
) AS ranked WHERE rn BETWEEN 21 AND 40;

6. Distinct Count

SELECT MAX(dense_rank) FROM (
    SELECT DENSE_RANK() OVER (ORDER BY salary) AS dense_rank FROM employees
) AS ranked;

7. Rank in Department

SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

8. Top with Ties

SELECT * FROM (
    SELECT *, RANK() OVER (ORDER BY score DESC) AS rnk FROM scores
) AS ranked WHERE rnk <= 3;

9. Latest per Group

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) AS ranked WHERE rn = 1;

10. Gap-Free Ranking

SELECT name, DENSE_RANK() OVER (ORDER BY level) AS rank FROM items;

Visual

The Three Functions

┌─────────────────────────────────────────────────────────────┐
│  Salaries: [100, 90, 90, 80]                                │
│                                                             │
│  ROW_NUMBER:  1, 2, 3, 4                                    │
│  RANK:        1, 2, 2, 4                                    │
│  DENSE_RANK:  1, 2, 2, 3                                    │
│                                                             │
│  ROW_NUMBER: unique, ties broken arbitrarily                │
│  RANK: ties share, next rank skips                          │
│  DENSE_RANK: ties share, no skips                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

ROW_NUMBER for the Unique Position

┌─────────────────────────────────────────────────────────────┐
│  ROW_NUMBER() OVER (ORDER BY price DESC)                    │
│                                                             │
│  ┌───────┬────┐                                             │
│  │ price │ rn │                                             │
│  ├───────┼────┤                                             │
│  │ 100   │ 1  │                                             │
│  │ 90    │ 2  │  ← tie                                      │
│  │ 90    │ 3  │  ← tie, different number                    │
│  │ 80    │ 4  │                                             │
│  └───────┴────┘                                             │
│                                                             │
│  Use for: the pagination, the top N distinct                │
│                                                             │
└─────────────────────────────────────────────────────────────┘

RANK for the Competition Rank

┌─────────────────────────────────────────────────────────────┐
│  RANK() OVER (ORDER BY score DESC)                          │
│                                                             │
│  ┌───────┬──────┐                                           │
│  │ score │ rank │                                           │
│  ├───────┼──────┤                                           │
│  │ 100   │ 1    │                                           │
│  │ 90    │ 2    │  ← tie                                    │
│  │ 90    │ 2    │  ← tie, same rank                         │
│  │ 80    │ 4    │  ← the rank 3 is skipped                  │
│  └───────┴──────┘                                           │
│                                                             │
│  Use for: the leaderboard, the top N with ties              │
│                                                             │
└─────────────────────────────────────────────────────────────┘

DENSE_RANK for the Gap-Free Rank

┌─────────────────────────────────────────────────────────────┐
│  DENSE_RANK() OVER (ORDER BY salary DESC)                   │
│                                                             │
│  ┌────────┬────────────┐                                    │
│  │ salary │ dense_rank │                                    │
│  ├────────┼────────────┤                                    │
│  │ 100    │ 1          │                                    │
│  │ 90     │ 2          │  ← tie                             │
│  │ 90     │ 2          │  ← tie, same rank                  │
│  │ 80     │ 3          │  ← no skip                         │
│  └────────┴────────────┘                                    │
│                                                             │
│  Use for: the nth highest, the distinct count               │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Top N per Group

┌─────────────────────────────────────────────────────────────┐
│  INNER QUERY                                                │
│                                                             │
│  SELECT product_id, category, price,                        │
│         ROW_NUMBER() OVER (                                 │
│           PARTITION BY category                             │
│           ORDER BY price DESC) AS rn                        │
│  FROM products;                                             │
│                                                             │
│  ┌────────┬──────────┬───────┬────┐                         │
│  │ id     │ category │ price │ rn │                         │
│  ├────────┼──────────┼───────┼────┤                         │
│  │ 1      │ A        │ 100   │ 1  │                         │
│  │ 2      │ A        │ 90    │ 2  │                         │
│  │ 3      │ A        │ 80    │ 3  │                         │
│  │ 4      │ B        │ 200   │ 1  │                         │
│  │ 5      │ B        │ 150   │ 2  │                         │
│  └────────┴──────────┴───────┴────┘                         │
│                                                             │
│  OUTER QUERY: WHERE rn <= 3                                 │
│                                                             │
│  The top 3 products per category.                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
`ROW_NUMBER()`	Unique sequential
`RANK()`	Ties share, gaps
`DENSE_RANK()`	Ties share, no gaps
`OVER`	Required
`ORDER BY`	Required in `OVER`
`PARTITION BY`	Optional, resets
Filter	Subquery + outer `WHERE`
Tiebreaker	For the deterministic `ROW_NUMBER`
Top N distinct	`ROW_NUMBER`
Top N with ties	`RANK`
Nth highest	`DENSE_RANK`

Key takeaways:

The three functions assign numbers based on the order. The ROW_NUMBER is the unique position. The RANK is the competition rank with the gaps. The DENSE_RANK is the gap-free rank.
The ties are the difference. The ROW_NUMBER breaks the ties arbitrarily, and the result is the non-deterministic unless the tiebreaker is added. The RANK and the DENSE_RANK give the tied rows the same rank, and they differ in the skip.
The OVER clause is required. The ORDER BY determines the rank order. The PARTITION BY resets the numbering for each group. The ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) is the standard pattern for the top N per group.
The ranking functions cannot be used in the WHERE. The window functions are evaluated after the WHERE. The filter on the rank requires the subquery: the inner query computes the rank, and the outer query filters.
The tiebreaker makes the ROW_NUMBER deterministic. The ORDER BY price DESC, id ASC ensures the same result across the runs. The non-deterministic ROW_NUMBER is the source of the flaky pagination and the inconsistent top N.
The choice depends on the ties. The ROW_NUMBER for the unique position and the top N distinct. The RANK for the leaderboard and the top N with the ties. The DENSE_RANK for the nth highest and the distinct count.
The DENSE_RANK is the “nth highest” function. The WHERE dense_rank = 2 returns all the rows with the second distinct value, and the WHERE row_number = 2 returns the single row in the second position.

Remember: The ranking functions are the tools for the position and the rank. The ROW_NUMBER is the unique number, the RANK is the competition rank, and the DENSE_RANK is the gap-free rank. The ties are the difference, and the tiebreaker is the determinism. The OVER clause is the requirement, and the subquery is the filter. The top N per group, the pagination, the deduplication, and the nth highest are the patterns. The choice depends on what the ties should mean and what the position should be.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!