PARTITION BY
- SQL course
- Advanced
- Lesson 17/81
- 4 min
Compute windows inside groups.
PARTITION BY
PARTITION BY divides the window into groups. Each group is computed separately.
SELECT name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
FROM products;Separate ranking per category.
name, category, price, RANK() OVER (PARTITION BY category … is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
PARTITION BY — details
PARTITION BY does not collapse like GROUP BY. Each row gets its own group's result.
For example, OVER (PARTITION BY category ORDER BY price DESC) gives number 1 to the most expensive product in each category; numbering restarts in every category.
Practice PARTITION BY
Rank within each category with RANK() OVER (PARTITION BY category ORDER BY price DESC).
Starter code
-- rank within category
SELECT name, category, price,
RANK() OVER (Sample solution
SELECT name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk
FROM products;Expected output
name category rk Laptop Elektronik 1 Kalem Kitap 1
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add PARTITION BY
RANK() OVER (____ category ORDER BY price DESC)RANK() OVER (PARTITION BY category ORDER BY price DESC)Explanation: PARTITION BY is the right answer.
2 PARTITION BY: match the terms
| Term | Meaning |
|---|---|
PARTITION BY |
group |
category |
section |
separate rank |
within group |
no collapse |
preserves |
3 What does PARTITION BY do?
- Splits rows into groups Correct answer
- Deletes rows
- Sorts them
- Filters
Explanation: PARTITION BY divides the window into groups.