Advanced Pagination or Top N per Group using ROW_NUMBER()

SELECT
  product_id,
  product_name,
  category_name,
  price
FROM (
  SELECT
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    p.price,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.price DESC) as rn
  FROM
    products p
  JOIN
    categories c ON p.category_id = c.id
) AS ranked_products
WHERE
  rn <= 3;
  -- To get the top 3 most expensive products per category

How it works: This snippet demonstrates how to use the `ROW_NUMBER()` window function to assign a sequential rank to rows within each partition (category in this case), ordered by price. By partitioning data and then filtering based on the rank (e.g., `rn <= 3`), you can efficiently retrieve the top 'N' items for each group, a common requirement for advanced reporting and non-LIMIT/OFFSET based pagination within groups.