ROW_NUMBER

Hook: Interviewers love ROW_NUMBER because it tests two things at once: can you order rows correctly, and do you understand why the result must be deterministic?

Question: What does ROW_NUMBER do in SQL?

Answer: ROW_NUMBER is a window function that assigns a unique number to each row after you tell SQL how to sort the rows. If you use PARTITION BY, the numbering restarts inside each group. It is most useful when you want the first row per group, de-duplication, or clean pagination.

Interview-Ready Answer: I use ROW_NUMBER when I need a unique sequence for rows based on a defined order. It starts at 1 and, if I add PARTITION BY, it restarts for each group. One important detail is that I always add a deterministic tie-breaker in the ORDER BY, because if two rows compare equal, the engine is free to pick either one first.

Detailed Explanation:

What it is

ROW_NUMBER() is a window function, meaning it looks at a set of rows related to the current row without collapsing them into one result. A plain aggregate like SUM() gives one value per group; ROW_NUMBER() keeps every row and just adds a sequence number.

How it works under the hood

1. SQL reads the rows from FROM, then applies WHERE, GROUP BY, and HAVING.
2. For each partition, the engine sorts rows by the ORDER BY inside OVER.
3. The first row in that order gets 1, the next gets 2, and so on.
4. If you use PARTITION BY, the counter restarts at 1 for each partition.
5. The result is added as a new column, and only after that does the final outer ORDER BY control display order.

That last point matters: the numbering order is defined by the window ORDER BY, not the outer query order. If you sort the final output differently, the row numbers do not change.

When and why to use it

• Top-N per group: keep only the newest order per customer, the highest score per student, or the most recent log row.
• De-duplication: pick one row from many duplicates and delete the rest.
• Pagination: number rows first, then filter a range such as 101 to 120.

ROW_NUMBER vs. RANK vs. DENSE_RANK

Memory rule: ROW_NUMBER is the only one that always gives a unique number to every row. If two rows tie, RANK and DENSE_RANK preserve the tie; ROW_NUMBER does not.

Performance and edge cases

The expensive part is usually sorting. The numbering itself is cheap, but sorting a million rows is not. In practice, window queries often behave like an O(n log n) sort plus an O(n) pass to assign numbers. If the data is already supported by an index that matches PARTITION BY and ORDER BY, the optimizer may do less work. If not, large partitions can spill to disk.

The biggest gotcha is non-determinism on ties. If your ORDER BY is not unique, the engine may return a different row as rn = 1 on different runs. Fix that by adding a stable tie-breaker, like a primary key.

Another common trap is trying to filter with WHERE rn = 1 in the same SELECT. Window functions are evaluated after WHERE, so you must wrap the query in a subquery or CTE first.

Real-World Example: Imagine a checkout service that stores every payment attempt. For each customer_id, the team wants the latest successful attempt so support can see the final charge state. They use ROW_NUMBER() partitioned by customer and ordered by event time descending, then keep rn = 1.

What goes wrong when someone forgets the tie-breaker? Two payment rows land in the same second, and the query sometimes picks the older row. Users see the wrong receipt total, support sees mismatched audit trails, and the logs show the same customer returning different row ids after a redeploy. That kind of bug is nasty because the SQL looks correct at first glance, but the sort order was not fully defined.

Follow-up & Tricky Questions:

Follow-up questions

• How is ROW_NUMBER different from RANK? ROW_NUMBER never ties: every row gets a unique number. RANK gives equal rows the same value and leaves gaps after ties.
• How do you get the latest row per group? Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) and keep rn = 1. Add a primary key as the last sort column so the result is stable.
• Why can’t I filter rn directly in WHERE? Because window functions are computed after WHERE. Put the window query in a subquery or CTE, then filter outside it.
• What happens if I omit PARTITION BY? The numbering runs across the entire result set as one big group, starting at 1.
• Does the outer ORDER BY change the row numbers? No. It only changes the final display order; the window ORDER BY controls the numbering itself.

Tricky / gotcha questions

• If two rows tie on the window ORDER BY, is the result stable? No. SQL does not guarantee which tied row gets the lower number unless you add another sort column that makes the order unique.
• Can ROW_NUMBER be used for pagination safely? Yes, but only with a deterministic sort and ideally a stable snapshot; otherwise rows can move between pages as data changes.
• Is ROW_NUMBER the same as numbering after GROUP BY? No. GROUP BY changes the row set; ROW_NUMBER numbers the existing rows after the grouping step is finished.

Common Mistakes:

• Forgetting a tie-breaker. Fix: add a unique column, usually the primary key, as the last item in the window ORDER BY.
• Using WHERE rn = 1 in the same SELECT. Fix: compute rn in a CTE or subquery, then filter outside.
• Confusing ROW_NUMBER with RANK. Fix: remember that ROW_NUMBER never repeats numbers, while RANK can.
• Assuming the final query order controls numbering. Fix: only the ORDER BY inside OVER decides the row numbers.

Memory Hook: Think of ROW_NUMBER as a deli ticket machine: you choose the line order first, then each row gets the next ticket, and the ticket count resets when a new line starts.

Cheat Sheet:

• Unique sequential numbers for rows.
• PARTITION BY = restart numbering per group.
• ORDER BY inside OVER = numbering rule.
• Add a tie-breaker for deterministic results.
• Filter with a subquery or CTE, not the same WHERE.
• Best for top-N, de-dup, and pagination.

Practice Tasks:

• Number all employees by salary from highest to lowest.
• Return the newest order per customer using rn = 1.
• Try removing the tie-breaker and observe why the winner among ties becomes unsafe.