Skip to content
SQLAdvanced SQL Queriesadvanced24 min read

Ranking with ROW_NUMBER, RANK, and DENSE_RANK

Assign sequential numbers and rankings to rows using ROW_NUMBER, RANK, and DENSE_RANK. Understand ties, rank gaps, top-N-per-group queries, deterministic ordering, and practical PostgreSQL ranking patterns.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Assign unique sequential numbers using ROW_NUMBER()
  • ✓Handle ties with gaps using RANK()
  • ✓Handle ties without gaps using DENSE_RANK()
  • ✓Explain how window ORDER BY determines ranking and how tie-breakers affect results
  • ✓Solve top-N-per-category and top-N-per-group queries using window rankings
  • ✓Choose an appropriate ranking function based on tie-handling requirements
  • ✓Use ranking functions safely for filtering, pagination, and reporting

Introduction to SQL Ranking Functions

## What are ranking functions?

Ranking functions assign a number or rank to each row based on an ordering rule. They are window functions, so they preserve the individual rows rather than collapsing them into group summaries.

Common uses include:

- Ranking students by marks.
- Finding the highest-paid employees in each department.
- Listing the top-selling products.
- Selecting the latest order for each customer.
- Creating numbered result sets for reporting and pagination.

SQL provides three commonly used ranking functions:

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()

All three can use OVER() with ORDER BY. The difference is how they assign numbers when multiple rows have equal ranking values.

## Example scores

Imagine these scores, ordered from highest to lowest:

| Student | Score |
|---|---:|
| Asha | 100 |
| Ravi | 90 |
| Meera | 90 |
| Kabir | 80 |

Ravi and Meera have the same score. A ranking function must follow its own rules for handling this tie.

The key question is whether you want a unique position for every row, competition-style ranks with gaps, or dense ranks without gaps.

Core Syntax and Ranking Rules

## Basic syntax

ROW_NUMBER() OVER (
ORDER BY score DESC
)

RANK() OVER (
ORDER BY score DESC
)

DENSE_RANK() OVER (
ORDER BY score DESC
)

ORDER BY score DESC means the highest score comes first.

### ROW_NUMBER()

Assigns a unique sequential number to each row:

1, 2, 3, 4

Tied scores still receive different row numbers. If the ORDER BY columns do not uniquely determine row order, the assignment among tied rows is not guaranteed to be stable.

### RANK()

Assigns the same rank to rows tied on the window's ordering values. The next rank skips positions.

For scores 100, 90, 90, 80:

1, 2, 2, 4

### DENSE_RANK()

Assigns the same rank to tied rows but does not leave a gap before the next distinct score.

For scores 100, 90, 90, 80:

1, 2, 2, 3

These functions do not sort the final query output by themselves. Add an outer ORDER BY when the result must be displayed in a specific order.

Compare All Three Ranking Functions

Compare All Three Ranking Functions
sql
WITH scores(student, score) AS (
    VALUES
        ('Asha', 100),
        ('Ravi', 90),
        ('Meera', 90),
        ('Kabir', 80)
)
SELECT
    student,
    score,
    ROW_NUMBER() OVER (
        ORDER BY score DESC, student
    ) AS row_num,
    RANK() OVER (
        ORDER BY score DESC
    ) AS rank_num,
    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS dense_rank_num
FROM scores
ORDER BY score DESC, student;
Output
student | score | row_num | rank_num | dense_rank_num --------+-------+---------+----------+--------------- Asha | 100 | 1 | 1 | 1 Meera | 90 | 2 | 2 | 2 Ravi | 90 | 3 | 2 | 2 Kabir | 80 | 4 | 4 | 3

ROW_NUMBER uses student as a tie-breaker, so Meera and Ravi receive different row numbers. RANK and DENSE_RANK use score alone, so both students receive rank 2. RANK skips rank 3, while DENSE_RANK assigns rank 3 to Kabir.

Understanding Ties and Tie-Breakers

## What counts as a tie?

For RANK and DENSE_RANK, rows are tied when their values are equal across all expressions in the window's ORDER BY.

Consider:

RANK() OVER (ORDER BY score DESC)

Two students with score 90 receive the same rank.

Now consider:

RANK() OVER (ORDER BY score DESC, student)

If the student names differ, the rows no longer tie because the complete ordering values differ. They receive separate ranks.

This distinction is important.

**Use the ranking columns to define business ties.** If equal scores should share a rank, rank by score alone. If every row must have a unique position, use ROW_NUMBER with a deterministic tie-breaker.

The final SELECT can still use a secondary ordering column for stable presentation without changing how RANK or DENSE_RANK defines ties.

## When to use each function

| Requirement | Recommended function |
|---|---|
| Give every row a unique position | ROW_NUMBER |
| Share ranks and leave gaps after ties | RANK |
| Share ranks without gaps | DENSE_RANK |
| Return exactly up to N rows per group | ROW_NUMBER |
| Include all rows tied within the first N positions | RANK, with the appropriate filter |
| Find the top N distinct score levels | DENSE_RANK |

The exact choice depends on the business rule. For example, top three employees can mean three employees, the first three competition positions, or the top three distinct salary levels.

Practical Example: Rank Employees by Salary

## Rank salaries within a department

Suppose a company wants to compare salaries within each department.

PARTITION BY department creates a separate ranking window for every department. ORDER BY salary DESC puts the highest salary first within each department.

The employee_id is used as a tie-breaker for ROW_NUMBER so that employees with equal salaries have a deterministic row-number order for the given dataset.

For RANK and DENSE_RANK, salary alone defines ties. Employees with the same salary share the same rank.

This is useful for salary reports, compensation analysis, and department-level comparisons.

Rank Employees Within Each Department

Rank Employees Within Each Department
sql
WITH employees(employee_id, employee_name, department, salary) AS (
    VALUES
        (1, 'Asha', 'Engineering', 90000),
        (2, 'Ravi', 'Engineering', 85000),
        (3, 'Meera', 'Engineering', 85000),
        (4, 'Kabir', 'Sales', 70000),
        (5, 'Neha', 'Sales', 75000)
)
SELECT
    employee_id,
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC, employee_id
    ) AS row_num,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank,
    DENSE_RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_dense_rank
FROM employees
ORDER BY department, salary DESC, employee_id;
Output
employee_id | employee_name | department | salary | row_num | salary_rank | salary_dense_rank ------------+---------------+-------------+--------+---------+-------------+------------------ 1 | Asha | Engineering | 90000 | 1 | 1 | 1 2 | Ravi | Engineering | 85000 | 2 | 2 | 2 3 | Meera | Engineering | 85000 | 3 | 2 | 2 5 | Neha | Sales | 75000 | 1 | 1 | 1 4 | Kabir | Sales | 70000 | 2 | 2 | 2

Each department has its own independent ranking because of PARTITION BY. Ravi and Meera tie on salary, so both receive rank 2 and dense rank 2. ROW_NUMBER assigns different numbers because employee_id breaks the tie.

Top N Rows Per Group

## Find the top two employees in every department

A common SQL interview problem is to find the highest-paid employees in each department.

A global LIMIT 2 would return only two employees across the entire company. It would not return two employees from every department.

Instead:

1. Use PARTITION BY department.
2. Assign ROW_NUMBER within each department.
3. Order by salary descending and a unique tie-breaker.
4. Filter row_num <= 2 in an outer query.

The ranking must be calculated before it is filtered. This is why a CTE or subquery is used.

### What if ties should be included?

If the requirement is to include everyone tied within the first two competition positions, use RANK() and filter rank_num <= 2.

If the requirement is to return employees in the top two distinct salary levels, use DENSE_RANK() and filter dense_rank_num <= 2.

These queries may return more than two rows per department when salary ties qualify.

Top N Query Using ROW_NUMBER

Use this pattern when you want at most N rows per group.

The outer query filters the row number calculated by the CTE. A stable unique tie-breaker makes the selection predictable when salaries are equal.

Return at Most Two Employees per Department

Return at Most Two Employees per Department
sql
WITH employees(employee_id, employee_name, department, salary) AS (
    VALUES
        (1, 'Asha', 'Engineering', 90000),
        (2, 'Ravi', 'Engineering', 85000),
        (3, 'Meera', 'Engineering', 85000),
        (4, 'Kabir', 'Sales', 70000),
        (5, 'Neha', 'Sales', 75000)
),
ranked AS (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, employee_id
        ) AS row_num
    FROM employees
)
SELECT
    employee_id,
    employee_name,
    department,
    salary,
    row_num
FROM ranked
WHERE row_num <= 2
ORDER BY department, row_num;
Output
employee_id | employee_name | department | salary | row_num ------------+---------------+-------------+--------+-------- 1 | Asha | Engineering | 90000 | 1 2 | Ravi | Engineering | 85000 | 2 5 | Neha | Sales | 75000 | 1 4 | Kabir | Sales | 70000 | 2

The ranking is calculated independently per department. Filtering row_num <= 2 returns at most two employees from each department. Ravi is selected ahead of Meera because employee_id is the tie-breaker.

Top N With Ties: RANK and DENSE_RANK

## Include tied employees

Suppose Engineering has salaries of 100000, 90000, 90000, and 80000.

Using RANK:

| Salary | Rank |
|---:|---:|
| 100000 | 1 |
| 90000 | 2 |
| 90000 | 2 |
| 80000 | 4 |

Filtering rank <= 2 returns the top salary and everyone tied at rank 2.

Using DENSE_RANK:

| Salary | Dense rank |
|---:|---:|
| 100000 | 1 |
| 90000 | 2 |
| 90000 | 2 |
| 80000 | 3 |

Filtering dense_rank <= 2 also returns the top two distinct salary levels.

In this example the same rows qualify, but the functions differ once later ranks are involved. For example, filtering rank <= 3 excludes rank 4, whereas filtering dense_rank <= 3 includes the third distinct salary level.

Use RANK for competition positions and DENSE_RANK for distinct value levels.

Compare Top Two Ranking Strategies

Compare Top Two Ranking Strategies
sql
WITH employees(employee_name, department, salary) AS (
    VALUES
        ('Asha', 'Engineering', 100000),
        ('Ravi', 'Engineering', 90000),
        ('Meera', 'Engineering', 90000),
        ('Kabir', 'Engineering', 80000)
),
ranked AS (
    SELECT
        employee_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_rank,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS salary_dense_rank
    FROM employees
)
SELECT
    employee_name,
    salary,
    salary_rank,
    salary_dense_rank
FROM ranked
WHERE salary_rank <= 2
ORDER BY salary DESC, employee_name;
Output
employee_name | salary | salary_rank | salary_dense_rank --------------+--------+-------------+------------------ Asha | 100000 | 1 | 1 Meera | 90000 | 2 | 2 Ravi | 90000 | 2 | 2

The query uses RANK <= 2 to include every employee tied within the first two competition positions. Kabir is excluded because the 80000 salary receives rank 4. Change the outer condition to salary_dense_rank <= 2 to select the top two distinct salary levels.

Handling NULL Values and Deterministic Ordering

## What happens when the ranking value is NULL?

NULL is not the same as zero. It generally means the value is missing or unknown.

In PostgreSQL, NULL values sort last for ORDER BY ... DESC by default and first for ORDER BY ... ASC by default. Other database engines may use different defaults.

If employees with unknown salaries should appear at the bottom, make that rule explicit:

ORDER BY salary DESC NULLS LAST

You can also exclude unknown salaries before ranking if the business requirement says they should not participate. These approaches are not equivalent: excluding them removes those rows from the ranked result.

## Stable row numbering

ROW_NUMBER needs a complete ordering to produce a predictable row assignment. If multiple rows tie on every ORDER BY expression, their relative numbering is not guaranteed.

For repeatable results, include a unique key:

ROW_NUMBER() OVER (
ORDER BY salary DESC NULLS LAST, employee_id
)

Do not add a unique key to RANK or DENSE_RANK when the business rule requires equal salaries to share a rank. Instead, keep the ranking expression based on salary and use a separate outer ORDER BY for presentation.

## Ranking is not deduplication

Ranking can help select one row per group, but it does not automatically remove duplicates. If you use ROW_NUMBER and filter to row_num = 1, the chosen row depends on the ordering rule. Define that rule carefully.

Pagination with ROW_NUMBER

## Numbered result sets

ROW_NUMBER can assign positions to records for numbered reports or pagination.

For example, a query can assign row numbers ordered by created_at DESC and then return rows 21 through 40.

However, LIMIT and OFFSET are often simpler for straightforward pagination in PostgreSQL. Large offsets can be expensive, and rows can shift between requests when data changes.

For stable results, use a deterministic order with a unique tie-breaker. For large datasets or frequently changing data, keyset pagination may be more suitable than large OFFSET values.

Do not treat a row number as a permanent identifier. It is calculated for the current query result and ordering.

Select a Numbered Page

Select a Numbered Page
sql
WITH products(product_id, product_name, price) AS (
    VALUES
        (1, 'Keyboard', 1200),
        (2, 'Mouse', 500),
        (3, 'Monitor', 9000),
        (4, 'Webcam', 2500),
        (5, 'Headphones', 1800)
),
numbered AS (
    SELECT
        product_id,
        product_name,
        price,
        ROW_NUMBER() OVER (
            ORDER BY price DESC, product_id
        ) AS row_num
    FROM products
)
SELECT
    product_id,
    product_name,
    price,
    row_num
FROM numbered
WHERE row_num BETWEEN 2 AND 4
ORDER BY row_num;
Output
product_id | product_name | price | row_num -----------+--------------+-------+-------- 4 | Webcam | 2500 | 2 5 | Headphones | 1800 | 3 1 | Keyboard | 1200 | 4

The rows are numbered by price from highest to lowest. Filtering row numbers 2 through 4 returns the second, third, and fourth records in that ordering. The unique product_id provides a tie-breaker.

Common Mistakes

## Mistake 1: Using the wrong ranking function

ROW_NUMBER is for unique row positions. RANK is for competition-style ranking with gaps. DENSE_RANK is for ranking distinct levels without gaps.

Choosing the wrong function can change how many rows qualify in a top-N query.

## Mistake 2: Adding a tie-breaker to the ranking when ties should remain

If equal salaries should share a rank, this is incorrect for that business requirement:

RANK() OVER (
ORDER BY salary DESC, employee_id
)

Because employee_id differs, employees with the same salary no longer tie.

Use RANK() OVER (ORDER BY salary DESC) instead.

## Mistake 3: Filtering the window alias in WHERE

A window result cannot normally be referenced in the WHERE clause of the same SELECT level. Calculate the rank in a CTE or subquery and filter it outside.

## Mistake 4: Using global LIMIT for top N per group

LIMIT 2 returns at most two rows overall, not two rows from every department. Use PARTITION BY and an outer filter on the ranking result.

## Mistake 5: Ignoring NULL sort order

Unknown values may appear at the top or bottom depending on the database and ordering direction. Specify NULLS FIRST or NULLS LAST in PostgreSQL when placement matters.

## Mistake 6: Assuming row numbers are permanent

Ranking is calculated from the current input and ordering. Inserts, deletes, or changed values can change the row numbers.

## Mistake 7: Assuming ranking automatically improves performance

Ranking large datasets can require sorting or other substantial processing. Inspect the query plan and measure performance with representative data.

Practice Exercises

Use PostgreSQL to solve these exercises.

### Exercise 1: Compare all three functions

Given scores 100, 90, 90, 80, and 70, calculate ROW_NUMBER, RANK, and DENSE_RANK.

Explain why the last two functions produce different results.

### Exercise 2: Department rankings

Create an employee dataset with at least two departments and some tied salaries.

Rank employees within each department. Ensure that the ranking functions preserve ties correctly.

### Exercise 3: Top three employees

Return at most three employees per department, ordered by salary descending.

Use ROW_NUMBER and a unique tie-breaker.

### Exercise 4: Top three competition positions

Return every employee whose competition rank is at most 3.

Use RANK and explain why the output can contain more than three rows per department.

### Exercise 5: Top three distinct salaries

Return employees whose salaries belong to the top three distinct salary levels in each department.

Use DENSE_RANK.

### Exercise 6: Unknown salaries

Modify your ranking query so employees with NULL salaries appear last.

Explain the difference between using NULLS LAST and excluding NULL salaries.

### Exercise 7: One latest order per customer

Given an orders table with customer_id, order_id, and order_date, select each customer's latest order.

Use ROW_NUMBER partitioned by customer_id, ordered by order_date DESC and order_id DESC. Explain how this tie-breaker affects selection when dates match.

### Exercise 8: Pagination

Assign row numbers to products ordered by price descending, then return rows 11 through 20.

Explain why the ordering should include a unique tie-breaker.

### Exercise 9: Diagnose the tie-breaker

A developer writes RANK() OVER (ORDER BY salary DESC, employee_id) but expects equal salaries to share the same rank.

Explain the error and correct the expression.

### Exercise 10: Performance

Run EXPLAIN on a ranking query over a realistic dataset. Identify sorting or other expensive operations and discuss whether an index could help. Do not assume an index will eliminate all ranking costs.

Knowledge Check

### Question 1

Which function assigns a unique sequential number to every row?

A. RANK()
B. DENSE_RANK()
C. ROW_NUMBER()
D. COUNT()

**Answer:** C. ROW_NUMBER assigns a different number to each row.

### Question 2

For scores 100, 90, 90, and 80, what does RANK return?

A. 1, 2, 3, 4
B. 1, 2, 2, 4
C. 1, 2, 2, 3
D. 1, 1, 2, 3

**Answer:** B. RANK assigns equal ranks to tied rows and skips the next position.

### Question 3

For the same scores, what does DENSE_RANK return?

A. 1, 2, 2, 3
B. 1, 2, 2, 4
C. 1, 2, 3, 4
D. 1, 1, 1, 1

**Answer:** A. DENSE_RANK does not leave gaps after ties.

### Question 4

Which function is most suitable for returning at most three rows per department?

A. ROW_NUMBER()
B. RANK() in every situation
C. DENSE_RANK() in every situation
D. SUM()

**Answer:** A. ROW_NUMBER combined with a partition and an outer filter returns at most three rows per department.

### Question 5

What does PARTITION BY department do?

A. Removes all duplicate departments
B. Ranks all employees globally
C. Creates a separate ranking window for each department
D. Sorts only the final output

**Answer:** C. Each department is ranked independently.

### Question 6

What happens if employee_id is added to RANK's ORDER BY when ranking equal salaries?

A. Equal salaries always retain the same rank.
B. Different employee IDs can break the tie.
C. The query becomes an aggregate.
D. All NULL values are removed.

**Answer:** B. The full set of ORDER BY expressions determines whether rows tie.

### Question 7

Why use a CTE for top-N filtering?

A. Window functions cannot be used in SQL.
B. It lets the outer query filter the calculated ranking value.
C. It guarantees an index scan.
D. It removes the need for ORDER BY.

**Answer:** B. The rank is calculated first and filtered by the outer query.

### Question 8

Does RANK() guarantee the final result order?

A. Yes
B. No; use an outer ORDER BY
C. Only with NULL values
D. Only when the table has a primary key

**Answer:** B. Ranking order and final presentation order are separate.

SQL Interview Questions

### 1. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER assigns unique sequential numbers. RANK gives tied rows equal ranks and leaves gaps after ties. DENSE_RANK gives tied rows equal ranks without leaving gaps.

### 2. What is a tie in a ranking function?

Rows tie when their values are equal across all expressions in the ranking function's window ORDER BY.

### 3. How do you find the top three salaries in each department?

Use DENSE_RANK partitioned by department and ordered by salary descending, then filter dense_rank <= 3 if the requirement means the top three distinct salary levels. Use ROW_NUMBER if exactly up to three employees per department are required.

### 4. Why can a top-N query return more than N rows?

When RANK or DENSE_RANK is used, tied rows can share qualifying ranks. Filtering by rank can therefore return more than N rows.

### 5. Can ranking functions be used with PARTITION BY?

Yes. PARTITION BY creates independent windows, allowing separate rankings within departments, categories, customers, or other groups.

### 6. Why does adding a unique ID to RANK's ORDER BY change tie behavior?

Rows are tied only if every ordering expression has equal values. A unique ID makes the ordering values different, so those rows receive separate ranks.

### 7. How do you filter on ROW_NUMBER?

Calculate it in a CTE or subquery, then filter the calculated result in the outer query.

### 8. How do you select the latest order for each customer?

Use ROW_NUMBER partitioned by customer_id, ordered by order_date descending and a suitable unique tie-breaker descending. Filter to row number 1.

### 9. What happens when the ranking column contains NULL?

The rows can still be ranked. Their position depends on the database's NULL ordering rules and the ORDER BY direction. In PostgreSQL, explicitly use NULLS FIRST or NULLS LAST when necessary.

### 10. Are ranking values stable between queries?

Not necessarily. Changes to data or ordering can change rankings. ROW_NUMBER is particularly sensitive to ties unless the ordering is deterministic.

### 11. What is the difference between global top N and top N per group?

Global top N selects from the entire result. Top N per group uses PARTITION BY and filters a rank calculated independently in each group.

### 12. What performance considerations apply to ranking functions?

Large ranking operations may require sorting and processing substantial numbers of rows. Reduce unnecessary input rows, use appropriate ordering and indexes where useful, inspect EXPLAIN, and test realistic data.

Lesson Summary

SQL ranking functions let you assign sequential positions or ranks without collapsing rows.

Remember:

- ROW_NUMBER assigns a unique sequential number to every row.
- RANK assigns equal ranks to ties and leaves gaps.
- DENSE_RANK assigns equal ranks without gaps.
- PARTITION BY creates independent rankings for each group.
- The complete window ORDER BY determines tie behavior.
- Use ROW_NUMBER for at-most-N-row requirements.
- Use RANK for competition positions and DENSE_RANK for distinct value levels.
- Calculate rankings in a CTE or subquery before filtering them.
- Use explicit NULL ordering and deterministic tie-breakers where required.
- Validate performance with realistic data and query plans.

You should now be able to select the right ranking function, explain ties, and solve top-N-per-group interview problems.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Choosing a ranking function without considering ties

Why it happens: The developer assumes all three functions assign numbers in the same way.

Incorrect Approach
sql
sql
-- This does not preserve equal salaries as ties
-- because employee_id differs.
RANK() OVER (
    ORDER BY salary DESC, employee_id
)
Correct Solution
sql
sql
-- Equal salaries share the same rank.
RANK() OVER (
    ORDER BY salary DESC
)
 
-- Unique row positions with a stable tie-breaker.
ROW_NUMBER() OVER (
    ORDER BY salary DESC, employee_id
)

Key takeaway: Use the ranking function and ordering expressions that match the business requirement. A unique tie-breaker is appropriate for deterministic row numbering but can remove ties from RANK and DENSE_RANK.

#2 Using LIMIT to get the top N rows from every department

Why it happens: The developer confuses a global row limit with a per-group limit.

Incorrect Approach
sql
sql
SELECT employee_id, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Correct Solution
sql
sql
WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, employee_id
        ) AS row_num
    FROM employees
)
SELECT employee_id, department, salary
FROM ranked
WHERE row_num <= 3
ORDER BY department, row_num;

Key takeaway: The window partitions rows by department, and the outer filter selects up to three rows from each partition.

#3 Filtering a ranking alias in the same query's WHERE clause

Why it happens: The developer expects the window function's SELECT-list alias to be available to WHERE.

Incorrect Approach
sql
sql
SELECT
    employee_id,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_num
FROM employees
WHERE row_num <= 3;
Correct Solution
sql
sql
WITH ranked AS (
    SELECT
        employee_id,
        ROW_NUMBER() OVER (
            ORDER BY salary DESC, employee_id
        ) AS row_num
    FROM employees
)
SELECT employee_id, row_num
FROM ranked
WHERE row_num <= 3
ORDER BY row_num;

Key takeaway: Calculate the ranking in an inner query and filter it in the outer query.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • ROW_NUMBER assigns a different sequential number to every row in a window.
  • RANK assigns equal ranks to tied rows and leaves gaps after ties.
  • DENSE_RANK assigns equal ranks to tied rows without leaving gaps.
  • The ORDER BY inside OVER() determines the ranking order.
  • Adding a unique tie-breaker to a ranking function changes which rows count as tied.
  • Use a CTE or subquery to filter a calculated ranking value.
  • ROW_NUMBER is useful when a query must return at most N rows per group; RANK and DENSE_RANK can return more than N rows when ties qualify.
  • Stable pagination requires a deterministic ordering, usually including a unique key.
Was this lesson helpful?
Found an error? Report an issue