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()?
Initializing database...Each run starts from fresh sample data.
More Examples
Top earner per department
Find the highest-paid employee in each department.
Initializing database...Each run starts from fresh sample data.
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.