1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
SQL ROW_NUMBER explained with top N per group, deduping and pagination queries

Key takeaways

  • SQL ROW_NUMBER numbers rows 1, 2, 3 inside each window, with no gaps and no repeats, even when values tie.
  • Add PARTITION BY to 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 id to the ORDER BY.
  • You cannot filter on ROW_NUMBER in WHERE. Wrap it in a subquery or CTE; SQLite told us misuse 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 WHERE before 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 MAX subquery or LATERAL join 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Levenshtein Distance: Formula, Worked Table and Python Code

Levenshtein distance explained: the edit operations, the dynamic programming table for kitten and sitting, Python code with two-row memory, normalised…

Read article →
Software Development

URL Encoding Explained: Spaces, Plus Signs and UTF-8

URL encoding explained with tested Python and JavaScript code: percent encoding, reserved characters, %20 vs plus, UTF-8 bytes, encodeURI vs…

Read article →
DevOps

Gitignore: Patterns, Negation and Fixing Tracked Files

A tested gitignore guide: pattern rules, leading and trailing slashes, negation with !, the ignored parent folder trap, git check-ignore…

Read article →
Software Testing

Acceptance Criteria: Given When Then Examples and Checklist

How to write acceptance criteria testers can run: rule-based and Given When Then formats, a worked discount code example turned…

Read article →
Software Testing

State Transition Testing: Diagram, Table and Example

State transition testing explained with a login lockout example: states, events, the diagram, the state table, valid and invalid transitions,…

Read article →
Databases & SQL

Second Highest Salary in SQL: 5 Tested Ways and the Tie Trap

Find the second highest salary in SQL five ways: MAX subquery, LIMIT OFFSET, DENSE_RANK, a correlated subquery and per department,…

Read article →
Databases & SQL

SQL ROW_NUMBER: Top N per Group, Dedupe and Pagination

SQL ROW_NUMBER explained with tested queries: PARTITION BY, top N per group, deleting duplicates, pagination, the tie trap and how…

Read article →
Software Development

JavaScript Set: Union, Intersection and Removing Duplicates

JavaScript Set explained with tested examples: add, has, delete, deduplicating arrays, the new union and intersection methods, equality rules and…

Read article →
Test Automation

Python Unittest: Assert Methods, setUp and Mocks With Examples

A practical Python unittest tutorial: TestCase, assert methods, setUp and tearDown, subTest, skips, expected failures and mock, with a test…

Read article →
Software Development

Python Map and Filter: Examples vs List Comprehensions

Python map and filter explained with tested examples: lazy map objects, multiple iterables, filter(None), chaining, and a timed comparison with…

Read article →
Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →