ROW_NUMBER(): Numbering Rows Without Losing Them
All lessons
- ROW_NUMBER(): Numbering Rows Without Losing ThemFree
- PARTITION BY: Top-N per Group Locked
- RANK, DENSE_RANK and ROW_NUMBER on Ties Locked
- LAG and LEAD: Comparing with the Previous Row Locked
- Running Totals with SUM() OVER Locked
- Window Frames: Moving Averages with ROWS BETWEEN Locked
- ROWS vs. RANGE: The Default Frame Trap Locked
- Partition Totals and Shares Without GROUP BY Locked
- NTILE, PERCENT_RANK and CUME_DIST Locked
- FIRST_VALUE, LAST_VALUE and the Frame Trap Locked
- Gaps and Islands: Finding Streaks Locked
- Recursive CTEs: Generating a Series Locked
- Recursive CTEs: Walking a Hierarchy Locked
- Index Selectivity and Optimizer Statistics Locked
- B-tree Height and the Index vs. Scan Decision Locked
- Composite Indexes and the Leftmost-Prefix Rule Locked
- Sargable Predicates and Date Ranges Locked
- Join Algorithms: Nested Loop vs. Hash Join Locked
- Normalisation: Redundancy, Anomalies and Size Locked
- Transactions, Lost Updates and Isolation Levels Locked
- Final Project: A Regional Revenue Report Locked
You are not signed in — the lab works, but your progress is not saved. Sign up
Read first
The SQL
SELECT sale_id, rep, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM sales
ORDER BY rn;
-- top 3 sales: filter the window result in an outer query
SELECT SUM(amount)
FROM (SELECT amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM sales) AS t
WHERE rn <= 3;ROW_NUMBER is not deterministic when two rows tie on the ORDER BY columns. In production code add a unique tie-breaker, for example `ORDER BY amount DESC, sale_id`.
Loading the lab…