SQL
Efficient Data Ranking and Pagination with SQL Window Functions
Master SQL window functions like ROW_NUMBER() and RANK() to efficiently rank rows within partitions or implement advanced pagination logic for web applications.
SELECT
product_id,
product_name,
category_id,
total_sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total_sales DESC) as category_rank,
RANK() OVER (ORDER BY total_sales DESC) as overall_rank
FROM
products
WHERE
category_id = 101 -- Example: Filter for a specific category if needed
ORDER BY
category_id, category_rank;
How it works: This SQL snippet showcases the power of window functions for ranking data. `ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total_sales DESC)` assigns a unique, sequential rank to each product within its respective category, ordered by `total_sales` in descending order. `RANK() OVER (ORDER BY total_sales DESC)` provides an overall rank across all products, handling ties by assigning them the same rank. Window functions are invaluable for 'top N' queries, pagination, and analytics where you need to compare rows to other rows within a related set without collapsing the result set through grouping.