SQL ROW_NUMBER: Complete Guide with Examples (2026)

What Is ROW_NUMBER in SQL?

ROW_NUMBER() is a window function that assigns a unique sequential integer to each row in a result set. Numbering starts at 1 and increments by 1 for every row. No two rows ever receive the same number — even if their values are identical — which is what distinguishes ROW_NUMBER from RANK and DENSE_RANK.

The order of numbering is controlled by the ORDER BY clause inside OVER(). Add PARTITION BY and the numbering resets to 1 at the start of each partition, giving you independent row numbering per group.

ROW_NUMBER is part of the SQL standard (ISO/IEC 9075) and is supported in all major databases: MySQL 8+, PostgreSQL 8.4+, SQL Server 2005+, and SQLite 3.25+.

For a broader look at all window functions, see the SQL Window Functions Complete Guide.

Syntax

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

• ROW_NUMBER() takes no arguments.
• ORDER BY inside OVER() is required — it determines which row gets number 1, which gets 2, and so on.
• PARTITION BY is optional. When included, the numbering restarts at 1 for each partition.

A minimal example — number all employees globally by descending salary:

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

Output: the highest-paid employee gets rn = 1, the next gets rn = 2, and so on for every row regardless of department.

ROW_NUMBER vs RANK vs DENSE_RANK

All three are ranking window functions but they differ in how they handle tied values. Given employees with salaries of 90k, 80k, 80k, 70k:

salary	ROW_NUMBER	RANK	DENSE_RANK
90,000	1	1	1
80,000	2	2	2
80,000	3	2	2
70,000	4	4	3

• ROW_NUMBER — always unique. The two 80k rows get different numbers (2 and 3). The tie-breaking order for equal values is database-dependent unless you add a tiebreaker column to ORDER BY.
• RANK — tied rows share the same rank, then the next rank skips. After two rows ranked 2, the next row jumps to rank 4 (not 3).
• DENSE_RANK — tied rows share the same rank, but no ranks are skipped. After two rows ranked 2, the next row gets rank 3.

Choose ROW_NUMBER when you need a unique identifier for every row regardless of ties. Choose RANK or DENSE_RANK when the meaning of equal values matters and tied rows should share the same position.

ROW_NUMBER with PARTITION BY

Adding PARTITION BY makes ROW_NUMBER restart at 1 for each group. This is the most common form of the function in production queries.

-- Number each sale within its region, ordered by amount descending
SELECT
    salesperson,
    region,
    amount,
    sale_date,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rn_in_region
FROM sales
ORDER BY region, rn_in_region;

The North region gets its own sequence starting at 1. The South region gets its own independent sequence starting at 1. Without PARTITION BY, the numbering would run globally across all rows regardless of region.

For a deeper look at how PARTITION BY works, see the SQL PARTITION BY guide.

Top-N Per Group Pattern

Getting the top N rows within each group is one of the most frequently needed analytics queries. ROW_NUMBER with PARTITION BY is the standard solution.

-- Top 2 sales by amount within each region
WITH ranked AS (
    SELECT
        salesperson,
        region,
        amount,
        sale_date,
        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY amount DESC
        ) AS rn
    FROM sales
)
SELECT salesperson, region, amount, sale_date
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

The CTE assigns per-region row numbers ordered by amount DESC, so the highest sale in each region gets rn = 1. The outer query then filters to keep only rn <= 2.

You cannot use a window function directly in a WHERE clause — that is why the CTE (or a subquery) is necessary. The window function runs after FROM and WHERE are processed, so the filter must happen in an outer query.

Deduplication with ROW_NUMBER

Removing duplicate rows is one of the most practical uses of ROW_NUMBER. Partition by the columns that define a "duplicate" and order by a column that determines which row to keep (often a primary key or a timestamp). Any row with rn > 1 is a duplicate.

-- Find duplicates: same email, keep the lowest id
WITH dedup AS (
    SELECT
        id,
        email,
        name,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY id ASC
        ) AS rn
    FROM users
)
-- Preview what will be deleted
SELECT id, email, name FROM dedup WHERE rn > 1;

-- Delete the duplicates
DELETE FROM users
WHERE id IN (
    SELECT id FROM dedup WHERE rn > 1
);

The ORDER BY id ASC ensures the original row (lowest id) gets rn = 1 and is preserved. All later duplicates get rn > 1 and are deleted. Always preview with SELECT before running DELETE.

This pattern works on any database that supports ROW_NUMBER and CTEs. On databases that do not allow CTEs in DELETE (some older MySQL versions), move the CTE result into a temporary table first.

Pagination with ROW_NUMBER

Before OFFSET ... FETCH / LIMIT ... OFFSET became universally available, ROW_NUMBER was the standard way to implement pagination in SQL Server. It remains useful when you need stable, deterministic page slicing.

-- Page 2, 10 rows per page (rows 11-20)
WITH paginated AS (
    SELECT
        id,
        title,
        created_at,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM articles
)
SELECT id, title, created_at
FROM paginated
WHERE rn BETWEEN 11 AND 20
ORDER BY rn;

To make this dynamic, replace the hardcoded range with parameters: BETWEEN (@page - 1) * @page_size + 1 AND @page * @page_size. For page 1 (rows 1-10): WHERE rn BETWEEN 1 AND 10. For page 3 (rows 21-30): WHERE rn BETWEEN 21 AND 30.

On modern databases, OFFSET ... FETCH NEXT ... ROWS ONLY (standard SQL) or LIMIT ... OFFSET (MySQL/PostgreSQL) is simpler for basic pagination. The ROW_NUMBER approach is preferable when you need to reference the row position for other logic in the same query.

Database Compatibility

ROW_NUMBER is part of the SQL:2003 standard and is available in all major databases, though with different minimum version requirements.

Database	Minimum version	Notes
PostgreSQL	8.4 (2009)	Full window function support
SQL Server	2005	Full window function support
MySQL	8.0 (2018)	Not available in MySQL 5.x
MariaDB	10.2 (2017)	Full window function support
SQLite	3.25.0 (2018)	Older versions have no support

If you are on MySQL 5.x or an older SQLite and cannot upgrade, you can simulate row numbering with a user-defined variable (MySQL) or a correlated subquery, though these approaches are less readable and do not support PARTITION BY cleanly.

-- MySQL 5.x workaround using a variable (no window function needed)
SELECT
    name,
    department,
    salary,
    @row_num := @row_num + 1 AS rn
FROM employees, (SELECT @row_num := 0) AS init
ORDER BY salary DESC;

This variable-based approach does not support partitioning and the ordering is not guaranteed in all edge cases. Upgrading to MySQL 8.0+ to use native ROW_NUMBER is strongly recommended for any production use.

Frequently Asked Questions

What does ROW_NUMBER do in SQL?

ROW_NUMBER() assigns a unique sequential integer to each row in a partition, starting at 1. The order is defined by ORDER BY inside OVER(). Unlike RANK and DENSE_RANK, ROW_NUMBER never repeats a number — every row gets a distinct value even if the ordering column has duplicate values.

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

All three assign positions based on an ordering, but they differ on ties. ROW_NUMBER gives unique numbers regardless of ties (1, 2, 3, 4). RANK gives tied rows the same number and skips the next number (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3). Use ROW_NUMBER when you need a unique row identifier; use the others when tied values should share a position.

Does SQLite support ROW_NUMBER?

Yes, starting from SQLite version 3.25.0 (released September 2018). If you are on an older SQLite version you will need to upgrade, or use a correlated subquery to simulate row numbering. Check your version with SELECT sqlite_version();.

How do I use ROW_NUMBER for pagination in SQL?

Wrap your base query in a CTE that adds ROW_NUMBER() OVER (ORDER BY some_column) AS rn, then filter in the outer query with WHERE rn BETWEEN start AND end. For page 2 with 10 rows per page: WHERE rn BETWEEN 11 AND 20. On modern databases, OFFSET ... FETCH or LIMIT ... OFFSET is simpler for basic cases.