Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**ROW_NUMBER()** assigns unique sequential numbers (1,2,3...) even for ties. **RANK()** gives same rank for ties, gaps after (1,2,2,4). **DENSE_RANK()** same rank, no gaps (1,2,2,3). **Use ROW_NUMBER when**: Need exactly N rows per group (e.g., top 1 per user)—deterministic with...
ROW_NUMBER() assigns unique sequential numbers (1,2,3...) even for ties. RANK() gives same rank for ties, gaps after (1,2,2,4). DENSE_RANK() same rank, no gaps (1,2,2,3). Use ROW_NUMBER when: Need exactly N rows per group (e.g., top 1 per user)—deterministic with ORDER BY. Use RANK/DENSE_RANK when: Ties should share rank (e.g., leaderboards). Pro of ROW_NUMBER: Deterministic; predictable row count. Con: Arbitrary tie-break. Pro of DENSE_RANK: Handles ties meaningfully. Con: Can return more than N rows for "top N". Scalability: All are O(n) per partition; choose based on business rule.
Red Flag: ROW_NUMBER for 'top 3' when ties must be included—wrong semantics. Pro-Move: 'We use DENSE_RANK for leaderboards; ROW_NUMBER for dedup.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.