Skip to main content

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:

idnamedepartmentsalary
1AliceEngineering95000
2BobEngineering90000
3CharlieEngineering90000
4DianaSales70000
5EveSales65000
6FrankSales65000
7GraceSales60000
8HankMarketing72000
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], ...
)
info

ORDER BY inside the OVER() clause is required for ROW_NUMBER(). Without it, the assignment of numbers is non-deterministic, meaning the database can return rows in any order and number them arbitrarily.

Basic Example: Numbering All Rows​

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

Output:

namedepartmentsalaryrow_num
AliceEngineering950001
BobEngineering900002
CharlieEngineering900003
HankMarketing720004
DianaSales700005
EveSales650006
FrankSales650007
GraceSales600008

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:

namedepartmentsalaryrow_num
AliceEngineering950001
BobEngineering900002
CharlieEngineering900003
HankMarketing720004
DianaSales700005
EveSales650006
FrankSales650007
GraceSales600008

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

caution

When using ROW_NUMBER() without a tiebreaker on columns that contain duplicates, the results may vary between query executions. Always add a unique or nearly unique column (like a primary key or name) as a secondary sort to ensure consistent, reproducible results.

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:

namedepartmentsalarydept_row_num
AliceEngineering950001
BobEngineering900002
CharlieEngineering900003
HankMarketing720001
DianaSales700001
EveSales650002
FrankSales650003
GraceSales600004

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:

namedepartmentsalarysalary_rank
AliceEngineering950001
BobEngineering900002
CharlieEngineering900002
HankMarketing720004
DianaSales700005
EveSales650006
FrankSales650006
GraceSales600008

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:

namedepartmentsalarydept_rank
AliceEngineering950001
BobEngineering900002
CharlieEngineering900002
HankMarketing720001
DianaSales700001
EveSales650002
FrankSales650002
GraceSales600004

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:

namedepartmentsalarydense_salary_rank
AliceEngineering950001
BobEngineering900002
CharlieEngineering900002
HankMarketing720003
DianaSales700004
EveSales650005
FrankSales650005
GraceSales600006

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:

namedepartmentsalaryrow_numrankdense_rank
AliceEngineering95000111
BobEngineering90000222
CharlieEngineering90000322
HankMarketing72000443
DianaSales70000554
EveSales65000665
FrankSales65000765
GraceSales60000886
FunctionTies get same number?Gaps after ties?Max value equals...
ROW_NUMBER()NoN/ATotal row count
RANK()YesYesTotal row count
DENSE_RANK()YesNoNumber of distinct values
When to use which?
  • ROW_NUMBER(): When you need every row to have a unique number, regardless of ties. Best for pagination, deduplication, and top-N per group.
  • RANK(): When ties should share the same position and subsequent positions should reflect the actual count of rows above. Best for competition-style ranking (e.g., leaderboards).
  • DENSE_RANK(): When ties should share the same position but you do not want gaps. Best for finding the Nth distinct value (e.g., "the 3rd highest salary").

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:

namedepartmentsalaryrn
AliceEngineering950001
HankMarketing720001
DianaSales700001

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.
caution

Window functions cannot appear in a WHERE clause directly. You must always wrap the query in a subquery or a CTE first.

-- This will FAIL
SELECT name, department, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) = 1;

Error: window functions are not allowed in WHERE

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:

namedepartmentsalary
AliceEngineering95000
BobEngineering90000
HankMarketing72000
DianaSales70000
EveSales65000

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:

namedepartmentsalaryrnk
AliceEngineering950001
BobEngineering900002
CharlieEngineering900002
HankMarketing720001
DianaSales700001
EveSales650002
FrankSales650002

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:

idemaillogin_datesource
1alice@example.com2024-03-01web
2alice@example.com2024-03-01web
3bob@example.com2024-03-02mobile
4bob@example.com2024-03-02mobile
5bob@example.com2024-03-05web
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:

idemaillogin_datesource
1alice@example.com2024-03-01web
3bob@example.com2024-03-02mobile
5bob@example.com2024-03-05web

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.

caution

Always run the SELECT version of a deduplication query first to verify which rows will be kept and which will be removed before executing any DELETE statement. Deletes are permanent.

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:

namesalary
Hank72000

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:

namesalary
(empty result set, 0 rows)

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;
namesalaryposition
Alice95000.001
Bob90000.002
Charlie90000.003
Hank72000.004
Diana70000.005
Eve65000.006
Frank65000.007
Grace60000.008

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;
namesalaryposition
Alice95000.001
Bob90000.002
Charlie90000.002
Hank72000.003
Diana70000.004
Eve65000.005
Frank65000.005
Grace60000.006

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:

departmentrn
Engineering1
Marketing2
Sales3

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);
namesalaryrnrnkdrnk
Alice95000.00111
Bob90000.00222
Charlie90000.00322
Hank72000.00443
Diana70000.00554
Eve65000.00665
Frank65000.00765
Grace60000.00886

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.

tip

A simple mental model: imagine a classroom of students sorted by test score. ROW_NUMBER() is the seat number (every student sits in a different seat). RANK() is the competition placement (ties share a place, next place skips). DENSE_RANK() is the "distinct score position" (ties share, no skipping).

Table of Contents