PARTITION BY — Window Functions Within Groups

PARTITION BY divides rows into groups and applies the window function within each group separately.

Without PARTITION BY

-- Ranks ALL products together
SELECT name, category, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) as overall_rank
FROM products;

With PARTITION BY

-- Ranks products WITHIN each category
SELECT name, category, price,
       ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC
       ) as category_rank
FROM products;

Visualization

name	category	price	category_rank
Laptop	Electronics	999	1
Tablet	Electronics	499	2
Desk	Furniture	350	1
Chair	Furniture	150	2

Notice: ranking restarts for each category!

💡 The Difference

Approach	What it does
<code>OVER (ORDER BY price DESC)</code>	Rank across ALL rows
<code>OVER (PARTITION BY category ORDER BY price DESC)</code>	Rank within each group

🧠 Think of PARTITION BY as...

GROUP BY for window functions! But unlike GROUP BY, it keeps all rows.

Your Task

Rank products within each category by price (highest = 1). Show name, category, price, and category rank.