SQL Interview Prep · Lesson

ROW_NUMBER for Unique Sequencing

Assigning a deterministic row number within each partition.

ROW_NUMBER for Unique Sequencing is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What ROW_NUMBER Guarantees

ROW_NUMBER() assigns a unique, gapless integer to each row within its partition, starting at 1, following the window's ORDER BY. No two rows ever share a number, even when their ordering values are identical.

This uniqueness is exactly why interviewers reach for it to solve "pick one row per group" and deduplication problems. The other ranking functions (RANK, DENSE_RANK) do not promise uniqueness on ties.

Minimal ROW_NUMBER Query

The simplest form numbers an entire result set in a chosen order. ROW_NUMBER always needs an ORDER BY inside OVER — without it the numbering would be arbitrary, and most engines reject or warn on it.

Here, the most recently hired employee is row 1 if you order by hire date descending.

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

Numbering Within Partitions

Add PARTITION BY to number rows independently inside each group. The counter resets to 1 at every new partition value.

In the example, each department gets its own 1, 2, 3 sequence ordered by salary. The top earner in every department is number 1 — the seed of the top-N-per-group pattern.

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

ROW_NUMBER and Ties: There Are No Ties

Critical interview point: when two rows have equal ordering values, ROW_NUMBER still gives them different numbers. Which one gets the lower number is non-deterministic unless you add a tiebreaker column.

RANK would give tied rows the same rank.
ROW_NUMBER arbitrarily picks an order among them.

To make results reproducible, always add a unique tiebreaker to ORDER BY.

SELECT
  name,
  salary,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC, employee_id  -- employee_id breaks ties deterministically
  ) AS rn
FROM employees;

The Deterministic Tiebreaker Rule

If your ORDER BY is not a strict ordering (no column combination is unique), the row numbering can change between runs, even on the same data. Interviewers plant this trap in pagination and "latest record" questions.

Rule of thumb: append a primary key or other unique column as the final sort key whenever the result must be stable.

-- Unstable: many rows can share the same created_at
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

-- Stable: id guarantees a single deterministic winner
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC)

Worked Example: Latest Order Per Customer

A staple question: "Return each customer's most recent order." Number orders per customer by date descending, then keep rn = 1 in an outer query.

Because ROW_NUMBER guarantees exactly one row with number 1 per partition, you get precisely one row per customer — no duplicates even if two orders share a timestamp (the tiebreaker resolves it).

SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT
    customer_id, order_id, order_date, amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
) t
WHERE rn = 1;

Worked Example: Deduplicating Rows

ROW_NUMBER is the cleanest deduplication tool. Partition by the columns that define a duplicate, order by a preference rule, then keep rn = 1.

Here we treat rows with the same email as duplicates and keep the earliest-created one. Everything with rn > 1 is a duplicate you can delete or ignore.

SELECT id, email, created_at
FROM (
  SELECT
    id, email, created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at ASC, id ASC
    ) AS rn
  FROM users
) t
WHERE rn = 1;

ROW_NUMBER for Pagination

Before OFFSET/FETCH was universal, ROW_NUMBER drove pagination, and it still does in SQL Server and many ETL jobs. Number rows in a stable order, then filter a numbered range in an outer query.

Interview caution: pagination is only correct if the ordering is deterministic — otherwise the same row can appear on two pages or be skipped entirely.

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM articles
) t
WHERE rn BETWEEN 21 AND 40;  -- page 2, 20 per page

Assigning a Sequence to Re-Order Data

Sometimes you just need a sequential index, for example to re-number rows after a sort, build a 1..N label, or pair rows with a generated series. ROW_NUMBER over the desired order produces a clean dense sequence with no gaps.

This is also how you give an arbitrary unordered set a stable position for later joining.

SELECT
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id) AS leaderboard_position,
  player_id,
  score
FROM scores;

ROW_NUMBER vs COUNT for 'Nth Row'

When asked for "the 3rd most recent order" or "the 2nd row," reach for ROW_NUMBER and filter on the exact value in an outer query. Because numbering is unique, rn = 3 returns exactly one row.

Contrast with RANK: if you want the 2nd distinct value (e.g., second highest salary including ties), ROW_NUMBER is the wrong tool — that needs DENSE_RANK, covered next lesson.

SELECT order_id, order_date
FROM (
  SELECT order_id, order_date,
         ROW_NUMBER() OVER (ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders
) t
WHERE rn = 3;  -- exactly the 3rd most recent order

Pitfalls Recap

Keep these straight under interview pressure:

ROW_NUMBER is always unique and gapless within a partition.
It needs an ORDER BY in OVER; without a unique tiebreaker, results are non-deterministic on ties.
It cannot be filtered in WHERE — wrap it in a subquery/CTE.
Use it for one-row-per-group, deduplication, and pagination; use DENSE_RANK when ties must share a number.

Quick Check

How does ROW_NUMBER handle rows that tie on the ORDER BY value?

Recap: Deterministic Sequencing

ROW_NUMBER is your tool for unique, gapless numbering within partitions. You learned to:

Number whole sets and per-partition groups.
Add a unique tiebreaker for deterministic, reproducible results.
Solve latest-row-per-key, deduplication, pagination, and Nth-row problems by filtering rn in an outer query.

Next, you will see how RANK and DENSE_RANK deliberately give ties the same number — and how their gap behavior differs.

Frequently asked questions

Is the “ROW_NUMBER for Unique Sequencing” lesson free?

Yes — the full text of “ROW_NUMBER for Unique Sequencing” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “ROW_NUMBER for Unique Sequencing”?

Assigning a deterministic row number within each partition. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ROW_NUMBER for Unique Sequencing” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep