SQL ROW_NUMBER() Function
intermediatewindow-functions
2 min read
What is ROW_NUMBER()?
ROW_NUMBER() assigns a unique, sequential integer to each row within a partition. The numbering starts at 1 and increments by 1 based on the ORDER BY.
Syntax
ROW_NUMBER() OVER (
PARTITION BY partition_column
ORDER BY order_column
)When to Use
- Numbering rows for pagination
- Selecting the top N rows per group
- Deduplicating data (keep the first occurrence)
- Creating sequential identifiers in result sets
Key Points
- Always Unique — Unlike RANK, ROW_NUMBER never produces ties.
- Deterministic ORDER BY — If the ORDER BY does not produce a unique order, the row numbers among tied rows are non-deterministic.
- Top-N per Group — Use in a subquery:
WHERE rn = 1to get the first row per partition. - Pagination — ROW_NUMBER with a WHERE filter is a common pagination pattern.
- No PARTITION BY — Omitting PARTITION BY numbers all rows sequentially.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL ROW_NUMBER() Function Challenge
Write a query that solve this task: assign row numbers within each grade group.
Expected result
Students numbered 1, 2, 3, ... within each grade, ordered by name.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is ROW_NUMBER()?
Stuck? Reveal a hint:
Starting engine…
Press Run to execute the query once the engine is ready.
More Examples
Top earner per department
Find the highest-paid employee in each department.
Starting engine…
Press Run to execute the query once the engine is ready.
Frequently Asked Questions
What happens when two rows have the same ORDER BY value?
ROW_NUMBER still assigns different numbers, but the order among tied rows is arbitrary. Add more columns to ORDER BY for deterministic results.
How do I deduplicate rows using ROW_NUMBER?
Partition by the columns that define duplicates, order by a tiebreaker (like created_at), and keep only ROW_NUMBER() = 1.