SQL ROW_NUMBER: Top N per Group, Deduping and Pagination, Tested
SQL ROW_NUMBER gives every row a unique sequential number inside a window you define. Learn the syntax, PARTITION BY, the top N per group pattern, deleting duplicates, pagination, the tie trap, and how it compares with RANK and DENSE_RANK.
- SQL ROW_NUMBER numbers rows 1, 2, 3 inside each window, with no gaps and no repeats, even when values tie.
- Add
PARTITION BYto restart the count per group; that is how you get the top N rows per category. - Ties get an arbitrary order unless you add a unique tiebreaker such as
idto theORDER BY. - You cannot filter on ROW_NUMBER in
WHERE. Wrap it in a subquery or CTE; SQLite told usmisuse of window function ROW_NUMBER().
“Show the latest order per customer.” “Keep one row per email and delete the rest.” “Give me page 3 of the results.” Three common requests, one tool. SQL ROW_NUMBER is a window function that assigns a running number to rows, and once rows are numbered, picking the first one, the top three, or rows 21 to 30 becomes a simple filter.
We ran every query below on SQLite 3.50.4 through Python’s sqlite3 module. The syntax is standard SQL, so it also works on PostgreSQL, MySQL 8+, SQL Server and Oracle; the differences are noted where they matter. If window functions are new to you, start with our SQL window functions guide.
What ROW_NUMBER does
ROW_NUMBER looks at a set of rows, sorts them the way you say, and labels them 1, 2, 3 and so on. Unlike GROUP BY, it does not collapse rows. Every input row stays in the result and simply gains a new column. That makes it ideal for “pick one row per group” problems, where you need all the columns of the chosen row, not just an aggregate.
Sample data
CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, rep TEXT, amount INT, sold_on TEXT);
INSERT INTO sales VALUES
(1,'North','Asha',500,'2026-11-01'), (2,'North','Ben',700,'2026-11-02'),
(3,'North','Chen',700,'2026-11-03'), (4,'North','Dina',300,'2026-11-04'),
(5,'South','Eli',900,'2026-11-01'), (6,'South','Fay',400,'2026-11-02'),
(7,'South','Gus',650,'2026-11-05');
Ben and Chen both sold 700 in the North region. That tie is deliberate, because ties are where ROW_NUMBER surprises people.
ROW_NUMBER syntax
ROW_NUMBER() OVER (
[PARTITION BY column1, column2]
ORDER BY column3 [ASC | DESC], column4
)
The brackets mark optional parts. The function itself takes no arguments. Everything happens inside OVER: PARTITION BY splits rows into groups, and ORDER BY decides the numbering order within each group. A simple numbering of all sales by amount:
SELECT rep, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn FROM sales;
rep | amount | rn
Eli | 900 | 1
Ben | 700 | 2
Chen | 700 | 3
Gus | 650 | 4
Asha | 500 | 5
Fay | 400 | 6
Dina | 300 | 7
SQL Server and Oracle require the ORDER BY inside OVER for ROW_NUMBER. SQLite accepted ROW_NUMBER() OVER () without it, but the numbers then follow whatever order the engine happens to read rows in, which is not something to rely on.
Restarting the count with PARTITION BY
SELECT region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales;
region | rep | amount | rn
North | Ben | 700 | 1
North | Chen | 700 | 2
North | Asha | 500 | 3
North | Dina | 300 | 4
South | Eli | 900 | 1
South | Gus | 650 | 2
South | Fay | 400 | 3
The numbering restarts at 1 for South. Think of each partition as its own small table that gets numbered separately. You can partition by several columns, for example PARTITION BY region, product.
The tie trap and how to fix it
Ben and Chen tie at 700, yet one got 1 and the other got 2. Which one wins is not defined by the SQL standard. Microsoft documents ROW_NUMBER as nondeterministic for exactly this reason. The same query might return Chen first after an index change, a database upgrade, or on a different server. In a report this causes numbers that jump around; in a dedupe query it can delete a different row than yesterday.
The fix is to add a column that is unique to the end of the ORDER BY:
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id)
Now Ben (id 2) always beats Chen (id 3). Pick the tiebreaker with business meaning where possible, such as the earliest sold_on, and fall back to the primary key.
Tip: when a test that depends on ROW_NUMBER passes locally but fails in CI, look for missing tiebreakers first. It is a classic source of flaky tests.
ROW_NUMBER vs RANK vs DENSE_RANK
SELECT rep, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS rn,
RANK() OVER (ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM sales;
rep | amount | rn | rnk | drnk
Eli | 900 | 1 | 1 | 1
Ben | 700 | 2 | 2 | 2
Chen | 700 | 3 | 2 | 2
Gus | 650 | 4 | 4 | 3
Asha | 500 | 5 | 5 | 4
Fay | 400 | 6 | 6 | 5
Dina | 300 | 7 | 7 | 6
| Function | Ties | Gaps after ties | Use it for |
|---|---|---|---|
ROW_NUMBER |
different numbers | never | exactly one row per slot, dedupe, paging |
RANK |
same number | yes (1, 2, 2, 4) | competition rankings |
DENSE_RANK |
same number | no (1, 2, 2, 3) | Nth distinct value, such as second highest salary |
Our RANK vs DENSE_RANK guide goes deeper into the two ranking functions.
Top N rows per group
The most requested ROW_NUMBER pattern. Number the rows per group in a CTE, then filter:
WITH r AS (
SELECT region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
FROM sales
)
SELECT region, rep, amount FROM r WHERE rn <= 2;
region | rep | amount
North | Ben | 700
North | Chen | 700
South | Eli | 900
South | Gus | 650
Change rn <= 2 to rn = 1 for the single best row per group, such as the latest login per user (order by the login time descending). If you want every row that ties for first place, use RANK instead, because ROW_NUMBER returns exactly N rows per group by design. Our SQL CTE guide explains the WITH syntax.
Delete duplicate rows
A signups table has the same email several times. We want to keep the earliest row for each email:
SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created) AS rn
FROM signups ORDER BY email, rn;
id | email | rn
1 | a@example.com | 1
3 | a@example.com | 2
6 | a@example.com | 3
2 | b@example.com | 1
5 | b@example.com | 2
4 | c@example.com | 1
Every row with rn > 1 is a duplicate. Delete by ID through a subquery:
DELETE FROM signups WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created) AS rn
FROM signups
) AS d WHERE rn > 1
);
id | email | created
1 | a@example.com | 2026-11-01
2 | b@example.com | 2026-11-01
4 | c@example.com | 2026-11-02
Three rows remain, one per email. Always run the SELECT first and inside a transaction, so you can check what will go before it goes. MySQL does not let a DELETE subquery read the same table directly; the extra derived-table layer shown here is the usual workaround. Our delete duplicate rows guide compares this with other methods.
Pagination with ROW_NUMBER
Before OFFSET and FETCH were widely supported, ROW_NUMBER was the standard way to page results, and it is still useful when you need the row number itself in the output:
SELECT rep, amount FROM (
SELECT rep, amount, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM sales
) AS p WHERE rn BETWEEN 4 AND 6;
rep | amount
Dina | 300
Eli | 900
Fay | 400
That is page 2 with a page size of 3. Both this and OFFSET still read and discard the earlier rows, so deep pages get slow on big tables. For large data sets, keyset pagination is faster, as our SQL pagination guide shows with timings.
Why you cannot use ROW_NUMBER in WHERE
This looks reasonable but fails:
SELECT rep, amount FROM sales WHERE ROW_NUMBER() OVER (ORDER BY amount) = 1;
ERROR: OperationalError misuse of window function ROW_NUMBER()
SQL evaluates WHERE before window functions run, so the number does not exist yet at that point. The same applies to GROUP BY and HAVING. Compute the number in a subquery or CTE, then filter in the outer query, as every example above does. Snowflake, BigQuery and DuckDB offer a QUALIFY clause that filters window results directly, but SQLite, PostgreSQL, MySQL and SQL Server do not.
Window ORDER BY vs the query ORDER BY
The ORDER BY inside OVER controls the numbering. The ORDER BY at the end of the query controls how rows are displayed. They are independent:
SELECT rep, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM sales WHERE region = 'North' ORDER BY rep;
rep | amount | rn
Asha | 500 | 3
Ben | 700 | 1
Chen | 700 | 2
Dina | 300 | 4
Rows are listed alphabetically, but numbered by amount. Notice also that the WHERE filter ran first, so only North rows were numbered. If you filter after numbering instead, you get different numbers, so decide which you need.
Database support
- PostgreSQL, SQL Server, Oracle: supported for many years with the same syntax.
- MySQL: from version 8.0. MySQL 5.7 has no window functions; older code used session variables instead.
- SQLite: from version 3.25.
We only ran the queries on SQLite. The standard syntax shown is the same across these databases, but check your version before relying on it.
Performance tips
- An index on the partition and order columns, such as
(region, amount), can let the database avoid a full sort. - Filter rows in
WHEREbefore numbering when the logic allows, so fewer rows are sorted. - For “latest row per group” on a huge table, compare the ROW_NUMBER plan with a correlated
MAXsubquery orLATERALjoin using your database’s plan output; see our SQL query optimization guide.
Final thoughts
SQL ROW_NUMBER turns many tricky requests into one pattern: number the rows in a subquery or CTE, then filter on the number. Use PARTITION BY for per-group results, always add a unique tiebreaker so results are repeatable, and switch to RANK or DENSE_RANK when tied rows should share a position. With those habits, top N per group, dedupe and paging queries become routine.
Frequently asked questions
What does ROW_NUMBER do in SQL?
It assigns a unique sequential number, starting at 1, to each row within a window defined by OVER, ordered by the ORDER BY inside OVER. Tied rows still get different numbers.
What is the difference between ROW_NUMBER and RANK?
ROW_NUMBER never repeats a number, so tied rows get different values. RANK gives tied rows the same number and then skips, for example 1, 2, 2, 4.
How do I get the top 3 rows per group in SQL?
Number the rows with ROW_NUMBER over a PARTITION BY on the group column, ordered by the ranking column, in a CTE or subquery. Then select the rows where the number is 3 or less.
Why is ROW_NUMBER not allowed in a WHERE clause?
WHERE is evaluated before window functions, so the row number does not exist yet. Compute it in a subquery or CTE and filter in the outer query, or use QUALIFY where your database supports it.
Is ROW_NUMBER deterministic?
Only when the ORDER BY inside OVER produces a unique order. If rows tie, the database may number them differently between runs, so add a unique column such as the primary key as a tiebreaker.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.