SQL Interview Prep · Lesson

ROW_NUMBER for Unique Sequencing

Assigning a deterministic row number within each partition.

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;

All lessons in this course

← Back to SQL Interview Prep