SQL ROW_NUMBER(), RANK(), and DENSE_RANK() as Window Functions

When working with SQL, you will frequently need to number rows, rank results, or pick the top-N records per group. The three window functions designed exactly for these tasks are ROW_NUMBER(), RANK(), and DENSE_RANK(). They look similar at first glance, but each one handles tied values differently, and choosing the wrong one can silently produce incorrect results.

This guide explains how each function works, how they differ when ties occur, and walks you through the most common real-world use cases including top-N per group queries and row deduplication. Every concept includes practical examples with clear outputs so you can follow along immediately.

Prerequisites

Before diving in, you should be comfortable with the basics of SQL window functions and the OVER() clause. If terms like PARTITION BY or window frame feel unfamiliar, review an introductory guide to SQL window functions first.

All three functions covered here are dedicated window functions. They can only be used with the OVER() clause and have no standalone aggregate equivalent.

The Sample Data

Throughout this guide, we will use the following employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice',   'Engineering', 95000),
(2, 'Bob',     'Engineering', 90000),
(3, 'Charlie', 'Engineering', 90000),
(4, 'Diana',   'Sales',       70000),
(5, 'Eve',     'Sales',       65000),
(6, 'Frank',   'Sales',       65000),
(7, 'Grace',   'Sales',       60000),
(8, 'Hank',    'Marketing',   72000);

Notice that Bob and Charlie share the same salary (90000) and Eve and Frank share the same salary (65000). These ties are critical for understanding the differences between the three functions.

ROW_NUMBER()

ROW_NUMBER() assigns a unique, sequential integer to each row within its partition, based on the specified ORDER BY. No two rows in the same partition will ever receive the same number, even if they have identical values in the ordering column.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC], ...
)

Basic Example: Numbering All Rows

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Output:

Bob and Charlie both earn 90000, but ROW_NUMBER() still gives them different numbers (2 and 3). Which one gets 2 and which gets 3 is not guaranteed unless you add a tiebreaker column to the ORDER BY.

Adding a Tiebreaker

To make the numbering deterministic when values are tied, add a secondary sort column:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC, name ASC) AS row_num
FROM employees;

Output:

Now Bob always comes before Charlie because name ASC breaks the tie alphabetically.

Numbering Rows Within Partitions

The real power of ROW_NUMBER() emerges when combined with PARTITION BY. This restarts the numbering at 1 for each group:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC, name ASC
    ) AS dept_row_num
FROM employees;

Output:

Each department starts its own independent numbering sequence. This pattern is the foundation for top-N per group queries, which we will cover below.

RANK()

RANK() assigns a rank to each row within its partition based on the ORDER BY column. Unlike ROW_NUMBER(), when two or more rows have the same value, RANK() gives them the same rank. However, it then skips the subsequent rank number(s) to account for the tied positions.

Syntax

RANK() OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC], ...
)

Example

SELECT
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Output:

Key observations:

• Bob and Charlie both earn 90000 and both receive rank 2.
• Rank 3 is skipped entirely. The next rank after the tie is 4 (Hank).
• Eve and Frank both earn 65000 and both receive rank 6.
• Rank 7 is skipped. Grace gets rank 8.

Think of it like a race: if two runners tie for 2nd place, the next runner is in 4th place, not 3rd. There is no 3rd place.

RANK() with PARTITION BY

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

Output:

Within Sales, Eve and Frank tie at rank 2, so rank 3 is skipped and Grace receives rank 4.

DENSE_RANK()

DENSE_RANK() works like RANK() in that tied rows receive the same rank. The difference is that DENSE_RANK() does not skip any rank numbers. The next distinct value always gets the very next integer.

Syntax

DENSE_RANK() OVER (
    [PARTITION BY column1, ...]
    ORDER BY column2 [ASC|DESC], ...
)

Example

SELECT
    name,
    department,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;

Output:

Key observations:

• Bob and Charlie still share rank 2 (same as RANK()).
• Hank gets rank 3, not 4. No gap.
• Eve and Frank share rank 5.
• Grace gets rank 6, not 8. No gap.

The maximum DENSE_RANK() value tells you how many distinct salary levels exist in the data.

Side-by-Side Comparison

Let's put all three functions together in a single query to see the differences clearly:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC, name ASC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC)           AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC)           AS dense_rank
FROM employees;

Output:

Use Case: Top-N Per Group

One of the most common interview questions and real-world requirements is: "Find the top N records in each group." This is where ROW_NUMBER() (or sometimes RANK() / DENSE_RANK()) truly shines.

Problem: Find the Highest-Paid Employee in Each Department

SELECT * FROM (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

Output:

This pattern works by:

1. Assigning a row number within each department, ordered by salary descending.
2. Wrapping it in a subquery.
3. Filtering only rows where the row number is 1.

Top-N Using a CTE (Cleaner Syntax)

A Common Table Expression (CTE) makes the top-N pattern more readable:

WITH ranked_employees AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, name ASC
        ) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE rn <= 2;

Output:

This returns the top 2 earners per department. Marketing has only one employee, so only Hank is returned.

ROW_NUMBER vs RANK for Top-N: Which to Choose?

The choice depends on how you want to handle ties.

Using ROW_NUMBER() with WHERE rn <= 2 returns exactly 2 rows per group (or fewer if the group has fewer rows), even if there are ties.

Using RANK() with WHERE rnk <= 2 might return more than 2 rows per group if there are ties at the boundary:

WITH ranked_employees AS (
    SELECT
        name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT name, department, salary, rnk
FROM ranked_employees
WHERE rnk <= 2;

Output:

Engineering now returns 3 rows (Alice, Bob, and Charlie) because Bob and Charlie are tied at rank 2. Sales returns 3 rows as well. Use RANK() when you want to include all tied records at the boundary.

Use Case: Deduplication

Another extremely common scenario is removing duplicate rows. When a table has duplicate entries and you need to keep only one version of each, ROW_NUMBER() is the go-to tool.

The Problem

Consider a user_logins table with accidental duplicates:

CREATE TABLE user_logins (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    login_date DATE NOT NULL,
    source VARCHAR(20) NOT NULL
);
INSERT INTO user_logins (id, email, login_date, source) VALUES
(1, 'alice@example.com', '2024-03-01', 'web'),
(2, 'alice@example.com', '2024-03-01', 'web'),
(3, 'bob@example.com',   '2024-03-02', 'mobile'),
(4, 'bob@example.com',   '2024-03-02', 'mobile'),
(5, 'bob@example.com',   '2024-03-05', 'web');

Selecting Only Unique Rows

WITH deduplicated AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email, login_date, source
            ORDER BY id ASC
        ) AS rn
    FROM user_logins
)
SELECT id, email, login_date, source
FROM deduplicated
WHERE rn = 1;

Output:

How this works:

1. PARTITION BY email, login_date, source groups rows that are exact duplicates together.
2. ORDER BY id ASC determines which row to keep (the one with the smallest id).
3. Filtering WHERE rn = 1 keeps only the first occurrence and discards the rest.

Deleting Duplicates (SQL Server / PostgreSQL)

In some databases, you can delete duplicates directly using a CTE:

-- PostgreSQL syntax
WITH deduplicated AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email, login_date, source
            ORDER BY id ASC
        ) AS rn
    FROM user_logins
)
DELETE FROM user_logins
WHERE id IN (
    SELECT id FROM deduplicated WHERE rn > 1
);

This removes all rows where rn > 1, keeping only the first occurrence of each duplicate group.

Use Case: Finding the Nth Highest Value

DENSE_RANK() is the best choice when the question is "find the Nth distinct value" because it does not skip numbers.

Problem: Find the 3rd Highest Salary

WITH salary_ranks AS (
    SELECT
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
)
SELECT name, salary
FROM salary_ranks
WHERE dr = 3;

Output:

The distinct salary values in descending order are: 95000 (rank 1), 90000 (rank 2), 72000 (rank 3), 70000 (rank 4), 65000 (rank 5), 60000 (rank 6). DENSE_RANK() correctly identifies 72000 as the 3rd highest.

If you had used RANK() instead, the 3rd rank would be skipped (because 90000 is tied at rank 2, so RANK() jumps to 4), and WHERE dr = 3 would return no rows.

-- Using RANK() for comparison
WITH salary_ranks AS (
    SELECT
        name,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS r
    FROM employees
)
SELECT name, salary
FROM salary_ranks
WHERE r = 3;

Output:

This is why DENSE_RANK() is the correct function for "Nth highest/lowest" questions.

Common Mistakes to Avoid

Mistake 1: Missing ORDER BY in OVER()

All three ranking functions require ORDER BY to produce meaningful results:

-- Meaningless: no ORDER BY
SELECT name, salary,
    ROW_NUMBER() OVER () AS rn
FROM employees;

Without ORDER BY, the database assigns row numbers in an arbitrary order. The query will run, but the numbering is unpredictable and useless.

Fix: Always specify what the ranking should be based on.

SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

Mistake 2: Using ROW_NUMBER() When Ties Matter

If your business logic requires that tied values receive the same position, ROW_NUMBER() will silently give incorrect results because it always assigns unique numbers.

-- Problem: Bob and Charlie earn the same but get different row numbers
SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS position
FROM employees;

If Bob and Charlie should be ranked equally, use RANK() or DENSE_RANK() instead:

SELECT name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS position
FROM employees;

Mistake 3: Applying DISTINCT with Window Functions

Adding DISTINCT to a query with window functions often produces unexpected results because DISTINCT is applied after the window function has already been evaluated:

-- Probably not what you want
SELECT DISTINCT
    department,
    ROW_NUMBER() OVER (ORDER BY department) AS rn
FROM employees;

Since ROW_NUMBER() gives every row a unique number, every combination of (department, rn) is already unique, so DISTINCT removes nothing. If your goal is to list unique departments with a number, use a subquery or CTE with a GROUP BY or DISTINCT first, then apply the window function.

-- Correct approach
SELECT
    department,
    ROW_NUMBER() OVER (ORDER BY department) AS rn
FROM (
    SELECT DISTINCT department FROM employees
) unique_depts;

Output:

Performance Considerations

Ranking functions require the database to sort data within each partition. Keep these points in mind:

• Indexes matter. If you frequently rank by a specific column (e.g., salary), an index on that column (or a composite index including the PARTITION BY columns) can significantly speed up the query.
• Partition size matters. Very large partitions mean more data to sort. If performance is a concern, check whether your PARTITION BY clause creates reasonably sized groups.
• Avoid redundant window definitions. If you use multiple window functions with the same OVER() clause, some databases optimize them to share a single sort pass. You can also use a named window (in PostgreSQL and MySQL 8+):

SELECT
    name,
    salary,
    ROW_NUMBER() OVER w AS rn,
    RANK()       OVER w AS rnk,
    DENSE_RANK() OVER w AS drnk
FROM employees
WINDOW w AS (ORDER BY salary DESC);

This avoids repeating the same OVER() definition three times and can improve both readability and performance.

Summary

ROW_NUMBER(), RANK(), and DENSE_RANK() are three of the most frequently used SQL window functions for ordering and ranking data. Here is a final recap:

• ROW_NUMBER() assigns a unique sequential number. Every row gets a different number, even when values are tied. Best for top-N per group, pagination, and deduplication.
• RANK() assigns the same rank to tied values but skips subsequent numbers. Best for competition-style ranking where position reflects how many rows are above.
• DENSE_RANK() assigns the same rank to tied values with no gaps. Best for finding the Nth distinct value in a dataset.

All three require ORDER BY inside OVER() and cannot be used in WHERE directly. For filtering, always wrap them in a subquery or CTE.