SQL Window Functions
Window functions compute across related rows while keeping every row, which is perfect for ranking within groups.
GROUP BY collapses rows into one per group. A window function does something different: it computes a value across related rows butkeeps every row. That's exactly what you want for ranking.
row_number() over (partition by … order by …) numbers rows within each partition. PARTITION BY restarts the count for each group; ORDER BY inside the window decides who's number 1.
select team,
player,
points,
rank() over (partition by team order by points desc) as team_rank
from rosterRead the window in two parts
The function name says what to calculate. The OVER clause says which rows the calculation can see. In this lesson,row_number() creates the rank, while partition by p.category_id restarts that rank for each category.
A good check is the row count: window functions should keep the original grain unless you also add a filter. Here, all 24 products remain in the result, and each category starts again at rank 1.
Schema · Garden ShopTable · products9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
Rank products within each category by price. Returncategory_name, product_name, price, and a price_rank that is 1 for the most expensive product in each category. Order the output by category, then by rank.
- Columns: category_name, product_name, price, price_rank.
- Rows: all 24 products, each keeping its own row.
- price_rank restarts at 1 for the most expensive product in each category.