SQL ROW_NUMBER: Numbering Rows, and the Deduplication Trick

ROW_NUMBER() assigns a sequential integer to each row in a result set, restarting at 1 for each partition you define — and its most valuable use is not numbering rows for display, it is finding and removing duplicates, which it does better than almost anything else in SQL.

It is a window function, which means it operates over a set of rows related to the current row rather than collapsing them like an aggregate. That distinction explains both what it can do and the one rule that trips everyone up: you cannot reference it in a WHERE clause. Here is the syntax, the reason for that rule, and the patterns worth knowing.

Table of contents

• The syntax and what each part does
• Why you cannot use it in WHERE, and what to do instead
• The pattern it is genuinely best at: deduplication
• ROW_NUMBER versus RANK versus DENSE_RANK
• Performance, and the alternative worth knowing
• How this fits the rest of the stack
• FAQ

The syntax and what each part does

ROW_NUMBER() OVER (
    PARTITION BY column_a, column_b   -- optional: restart numbering per group
    ORDER BY column_c DESC            -- required: defines the numbering order
)

ORDER BY inside the OVER clause is what determines the numbering, and it is independent of any ORDER BY on the query itself. A query can number rows by date and then return them sorted by name.

PARTITION BY divides the rows into groups and restarts numbering at 1 for each. It is what makes the function useful — without it you get a plain sequence, and with it you get per-group ranking, which is where all the interesting patterns come from.

A simple example numbering employees by salary within each department:

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

The highest earner in each department gets 1, the next 2, and the count restarts for every department. The function is computed after the WHERE clause and GROUP BY have been applied, which is the key to the next section.

Why you cannot use it in WHERE, and what to do instead

This is the single most common mistake with window functions and the error message rarely explains it clearly.

-- This does not work.
SELECT name, salary
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;

The reason is the logical order in which SQL processes a query: FROM, then WHERE, then GROUP BY, then HAVING, then window functions, then SELECT, then ORDER BY. When WHERE runs, the window function has not been computed yet — there is nothing to compare against.

The fix is to compute it in a subquery or CTE and filter in an outer query, where the value now exists:

WITH ranked AS (
    SELECT
        name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, salary
FROM ranked
WHERE rn <= 10;

A CTE is clearer than a nested subquery and both perform identically in practice — query planners handle either. Use whichever your team reads more easily.

The same restriction applies to GROUP BY and HAVING for the same reason. Window functions are evaluated late, so anything that filters on them needs an extra layer.

The pattern it is genuinely best at: deduplication

This is the reason to know ROW_NUMBER() well. Partition by whatever makes a row a duplicate, order by whichever copy you want to keep, and everything numbered above 1 is surplus.

-- Inspect first. Always inspect first.
WITH dupes AS (
    SELECT
        id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(email))
            ORDER BY created_at ASC, id ASC
        ) AS rn
    FROM users
)
SELECT * FROM dupes WHERE rn > 1;

Read that carefully because each part is doing work. PARTITION BY LOWER(TRIM(email)) treats differently-cased and whitespace-padded addresses as the same person, which is usually what you mean by duplicate. ORDER BY created_at ASC, id ASC keeps the oldest record — change to DESC to keep the newest. The id tiebreaker makes the result deterministic when timestamps collide, which matters because without it, running the query twice can select different rows.

Once the selection looks right, delete:

-- Postgres
WITH dupes AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY LOWER(TRIM(email))
               ORDER BY created_at ASC, id ASC
           ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM dupes WHERE rn > 1);

Run the SELECT version first, every time, and read the rows it returns. A deduplication query with a wrong PARTITION BY deletes real data, and it deletes it in a way that looks deliberate.

In MySQL, DELETE with a CTE referencing the same table needs a join form instead — MySQL will not let you delete from a table it is selecting from in a subquery. Wrap the subquery in another SELECT or use a multi-table delete.

ROW_NUMBER versus RANK versus DENSE_RANK

Three functions, distinguished entirely by how they treat ties.

-- Scores: 100, 90, 90, 80
-- ROW_NUMBER(): 1, 2, 3, 4   -- always unique, ties broken arbitrarily
-- RANK():       1, 2, 2, 4   -- ties share a rank, then it skips
-- DENSE_RANK(): 1, 2, 2, 3   -- ties share a rank, no gap

Choose by intent:

• ROW_NUMBER() when you need exactly one row per group — deduplication, picking the latest record per entity, paging. Uniqueness is the point.
• RANK() for competition-style rankings where two people tied for second means nobody is third.
• DENSE_RANK() for the top N distinct values — the three highest salaries, however many people earn them.

The trap with ROW_NUMBER() is that its tie-breaking is arbitrary unless you make it deterministic. If two rows compare equal on your ORDER BY, which one gets 1 is up to the query planner and can change between runs, between versions, and between a sequential and a parallel scan. Always include a unique tiebreaker such as the primary key when the result matters.

Performance, and the alternative worth knowing

Window functions require the rows to be sorted within each partition. The single biggest performance factor is whether an index can supply that order.

An index matching (partition columns..., order columns...) — for the deduplication example, (email, created_at) — lets the database read rows in order and compute the numbering in one pass. Without one it sorts, which for a large table means spilling to disk.

Two practical points:

• Filter before the window function wherever possible. A WHERE clause in the CTE reduces the rows that need sorting; filtering only in the outer query does not.
• Read the plan. EXPLAIN ANALYZE will show whether a sort happened and whether it spilled to disk, which is the difference between fast and unusable on a large table.

For the specific case of latest-row-per-group on Postgres, DISTINCT ON is both shorter and frequently faster:

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;

It is Postgres-only and does exactly one thing, but it does that thing very well. ROW_NUMBER() remains the portable answer and the one to reach for when you need anything more than the first row per group — the top three, say, or a numbered result for paging.

How this fits the rest of the stack

Window functions on large tables are bounded by whether the sort fits in memory, which makes them a database-sizing question as much as a query one. The RunxBuild hosting calculator shows the database beside the service, storage and bandwidth so the plan is chosen with the workload in mind. RunxBuild runs managed MySQL and Postgres with backups, configurable connection limits, user management and private networking — both support the window functions above, and Postgres additionally supports DISTINCT ON.

Useful related references:

• n8n Google Sheets Integration: Auth, Operations, and the Row-Update Trap
• SQL Error 18456: Login Failed, and the State Number That Tells You Why
• What Is a Query in a Database? The Answer That Actually Helps You Write One
• Services on RunxBuild

FAQ

Why can I not use ROW_NUMBER in a WHERE clause?

Because of the logical order SQL evaluates a query: WHERE runs before window functions are computed, so there is no value to compare against. Compute ROW_NUMBER in a CTE or subquery and filter in an outer query where the column now exists. The same applies to GROUP BY and HAVING.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

They differ only in tie handling. ROW_NUMBER always produces unique numbers, breaking ties arbitrarily. RANK gives tied rows the same value then skips — 1, 2, 2, 4. DENSE_RANK gives tied rows the same value with no gap — 1, 2, 2, 3. Use ROW_NUMBER when you need exactly one row per group.

How do I delete duplicate rows with ROW_NUMBER?

Partition by the columns that define a duplicate, order by which copy to keep with a unique tiebreaker such as the primary key, then delete everything where the row number is greater than 1. Always run the SELECT version first and read the rows it returns before converting it to a DELETE.

Does ROW_NUMBER give consistent results?

Only if the ORDER BY inside the OVER clause is deterministic. If two rows compare equal, which one gets the lower number is up to the query planner and can vary between runs and between sequential and parallel execution. Include a unique column such as the primary key as a tiebreaker.

Is ROW_NUMBER slow on large tables?

It depends on whether an index can supply the required order. An index on the partition columns followed by the order columns lets the database compute the numbering in one pass; without one it sorts, which can spill to disk on a large table. Filter inside the CTE rather than only in the outer query, and check EXPLAIN ANALYZE.