Window Functions Ranking ROW_NUMBER · pagination · latest-per-group

ROW_NUMBER Window Function

Assign unique sequential integers within each partition. Ties don't share — every row gets its own number. The right tool for pagination, exact top-N reports, and the latest-record-per-group deduplication pattern.

What ROW_NUMBER does

ROW_NUMBER() assigns a unique sequential integer to each row in a partition, ordered by the ORDER BY clause. Unlike RANK or DENSE_RANK, ties don't share a number — every row gets its own.

This is the right tool when you need stable, unique numbers. For pagination ("rows 21 through 40"), for "the latest record per user", and any time you need exactly N rows per group regardless of ties.

Syntax

ROW_NUMBER() OVER (
[PARTITION BY column_list]
[ORDER BY column_list]
)

If you omit ORDER BY, the database is free to assign numbers in any order — fine for "give me a unique counter" but not when ordering matters.

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(20) NOT NULL,
    emp_address VARCHAR(20) NOT NULL,
    emp_salary  INT          NOT NULL,
    date_of_joining DATE     NOT NULL
);

INSERT INTO employees VALUES
(1, 'Raj',    'Pune',   20000, '2020-01-01'),
(2, 'King',   'Pune',   20000, '2020-01-01'),
(3, 'Thomas', 'Mumbai', 35000, '2020-02-01'),
(4, 'Harold', 'Mumbai', 45000, '2020-02-01'),
(5, 'Steve',  'Mumbai', 45000, '2020-02-09'),
(6, 'Anita',  'Delhi',  38000, '2022-11-20');

Example 1 — number rows by salary

SQL

SELECT emp_name,
       emp_salary,
       ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS row_num
FROM   employees;

Result

emp_name	emp_salary	row_num
Harold	45000	1
Steve	45000	2
Anita	38000	3
Thomas	35000	4
Raj	20000	5
King	20000	6

Notice that Harold and Steve — both at 45000 — get 1 and 2, not 1 and 1. ROW_NUMBER gives unique values; which tied row gets the lower number is up to the database.

Example 2 — number rows within each city

SQL

SELECT emp_name,
       emp_address,
       emp_salary,
       ROW_NUMBER() OVER (PARTITION BY emp_address
                          ORDER BY emp_salary DESC) AS rn
FROM   employees
ORDER  BY emp_address, rn;

Each city's rows get their own 1, 2, 3 sequence — perfect for "top N per group" reports.

Example 3 — exactly top 3 (ties broken arbitrarily)

SQL

WITH numbered AS (
    SELECT emp_name, emp_salary,
           ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS rn
    FROM   employees
)
SELECT * FROM numbered WHERE rn <= 3;

Always exactly three rows. The downside: if rows tie, you can't predict which one drops off the list. Use RANK instead if all tied rows should be included.

Example 4 — pagination

SQL

-- Rows 11..20 of an ordered list
WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY emp_id) AS rn
    FROM   employees
)
SELECT * FROM numbered WHERE rn BETWEEN 11 AND 20;

💡 For most pagination, OFFSET … LIMIT is simpler. ROW_NUMBER shines when you need to combine pagination with per-group filtering, or to deduplicate while keeping a specific row ("the latest entry per user").

Example 5 — keep the latest row per group

A common deduplication pattern. Use ROW_NUMBER to mark the latest row in each group, then keep only those.

SQL

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

Recap

ROW_NUMBER() assigns unique sequential integers within each partition.
Ties don't share numbers — order between tied rows is database-dependent.
Use for pagination, exact top-N, and "latest record per group" deduplication.
For ties to share their position, use RANK or DENSE_RANK instead.