SQL

Ranking Rows Within Groups Using Window Functions (ROW_NUMBER)

Master SQL window functions like ROW_NUMBER() and PARTITION BY to rank items within categories. Great for creating leaderboards or 'top N' data lists.

SELECT
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_category
FROM products;

How it works: This query utilizes the `ROW_NUMBER()` window function to assign a rank to each product within its specific category based on its price. The `PARTITION BY category_id` clause divides the dataset into independent groups for each unique `category_id`. Within each of these partitions, `ORDER BY price DESC` sorts the products by price in descending order. `rank_in_category` then assigns a sequential rank (1, 2, 3, etc.) to each product within its category based on this ordering, perfect for 'top N' analysis.

Ranking Rows Within Groups Using Window Functions (ROW_NUMBER)

Need help integrating this into your project?