SQL
Get Top N Records Per Group Without Window Functions
Learn a classic SQL technique to retrieve the top N records for each group (e.g., top 3 products per category) using subqueries and joins, ideal for older SQL versions or specific needs.
SELECT p1.product_id, p1.product_name, p1.category_id, p1.price
FROM products p1
INNER JOIN products p2 ON p1.category_id = p2.category_id AND p1.price <= p2.price
GROUP BY p1.product_id, p1.product_name, p1.category_id, p1.price
HAVING COUNT(DISTINCT p2.price) <= 3
ORDER BY p1.category_id, p1.price DESC;
-- Alternative using a correlated subquery (may vary in performance):
SELECT p.product_id, p.product_name, p.category_id, p.price
FROM products p
WHERE (
SELECT COUNT(*)
FROM products p_inner
WHERE p_inner.category_id = p.category_id
AND p_inner.price >= p.price
) <= 3
ORDER BY p.category_id, p.price DESC;
How it works: This snippet provides two methods to find the top N (e.g., 3) products per category based on price, without using advanced window functions (like `ROW_NUMBER()`). The first method uses a self-join and `GROUP BY` with `HAVING` to count how many distinct products in the same category have a price greater than or equal to the current product. The second uses a correlated subquery to achieve a similar result. Both are common techniques for this problem in environments where window functions are not available or preferred.