ROW_NUMBER, RANK and DENSE_RANK
All lessons
- ROW_NUMBER, RANK and DENSE_RANKFree
- Running Totals: ROWS vs the Default RANGE Frame Locked
- Moving Averages with Sliding Frames Locked
- LAG and LEAD: Comparing with Neighbour Rows Locked
- FIRST_VALUE, LAST_VALUE and the Frame Trap Locked
- NTILE, PERCENT_RANK and CUME_DIST Locked
- Top-N per Group and Deduplication with a CTE Locked
- Gaps and Islands: Finding Streaks Locked
- CROSS APPLY and OUTER APPLY Locked
- PIVOT, UNPIVOT and Conditional Aggregation Locked
- Clustered Indexes: Pages, Levels and Seeks Locked
- Nonclustered Indexes, Key Lookups and Covering Locked
- SARGable Predicates: Seek or Scan? Locked
- Reading Execution Plans: Cost and Estimates Locked
- Join Operators: Nested Loops, Merge and Hash Locked
- Statistics and Histograms: How Estimates Are Made Locked
- Parameter Sniffing and Plan Reuse Locked
- Isolation Levels: Dirty Reads and Phantoms Locked
- Locks, Blocking, Escalation and Deadlocks Locked
- Dynamic SQL Done Safely: sp_executesql and QUOTENAME Locked
- Final Project: A Window-Function Sales Report Locked
You are not signed in — the lab works, but your progress is not saved. Sign up
Read first
The SQL
SELECT order_id, region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region
ORDER BY amount DESC, order_id) AS rn,
RANK() OVER (PARTITION BY region
ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region
ORDER BY amount DESC) AS drnk
FROM dbo.Orders;Production rule: ROW_NUMBER() always needs a unique ORDER BY. Otherwise "the top row per group" can change between two runs of the same query.
Loading the lab…