Top N Per Group with LATERAL

basic

Get top 3 highest-priced products per category

-- PostgreSQL LATERAL syntax

SELECT c.category_name, p.product_name, p.price

FROM categories c,

LATERAL (

SELECT product_name, price

FROM products

WHERE category_id = c.category_id

ORDER BY price DESC

LIMIT 3

) p;

-- SQL Server CROSS APPLY syntax

SELECT c.category_name, p.product_name, p.price

FROM categories c

CROSS APPLY (

SELECT TOP 3 product_name, price

FROM products

WHERE category_id = c.category_id

ORDER BY price DESC

) p;

Returns top 3 products by price for each category

LATERAL/CROSS APPLY executes subquery for each category, referencing category_id from outer query.

PostgreSQL

Latest Orders Per Customer

intermediate

Find the 5 most recent orders for each customer

-- Get recent orders with LATERAL

SELECT

c.customer_name,

recent_orders.order_date,

recent_orders.total_amount

FROM customers c,

LATERAL (

SELECT order_date, total_amount

FROM orders o

WHERE o.customer_id = c.customer_id

ORDER BY order_date DESC

LIMIT 5