📎 This article includes 2 downloadable practice files ↓
In this article
GROUP BY squashes rows into one per group. Window functions calculate across a group but keep every row, which is what you need for rankings, “top N per group” and “latest per customer”.
function() OVER (PARTITION BY group_columns ORDER BY sort_columns)| Function | Ties (two at 90) | Use for |
|---|---|---|
| ROW_NUMBER | 1, 2, 3 (ties broken arbitrarily) | Picking exactly one row per group |
| RANK | 1, 1, 3 (gap) | Leaderboards, sports-style |
| DENSE_RANK | 1, 1, 2 (no gap) | “Second highest price” questions |
Top customer in each region
WITH t AS (
SELECT region, customer, SUM(amount) AS total,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
FROM sales
GROUP BY region, customer
)
SELECT region, customer, total FROM t WHERE rn = 1;You can’t write WHERE rn = 1 in the same query that creates rn, because window functions are computed after WHERE. Wrap it in a CTE, as above.
order_date DESC, order_id DESC) so the result is always the same.Common mistakes
- Forgetting PARTITION BY ranks over the whole table instead of within each region.
- Using ROW_NUMBER for “top 3” when ties matter: use RANK and
WHERE rnk <= 3.
Practice
Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 9 KB)
- 📄Practice database (SQLite)shop2.db: customers, products, 300 orders, payments, employees with managers and monthly targets.⬇ DB · 48 KB
- 🗃️Lesson 2 exercisesTasks with the real expected result under each, and answers at the bottom.⬇ SQL · 4 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.