Lateral Derived Tables for Row-Wise Calculations

Snippet

Lateral Derived Tables for Row-Wise Calculations

The LATERAL keyword (part of ANSI SQL) allows a subquery in the FROM clause to reference columns of preceding tables in the same FROM clause. This is powerful for 'top-N per group' queries or performing complex calculations for each row that require context from the outer table.

snippet.sql

sql

SELECT u.name, top_order.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT amount 
  FROM orders o 
  WHERE o.user_id = u.id 
  ORDER BY o.amount DESC 
  FETCH FIRST 1 ROW ONLY
) AS top_order;

Breakdown

CROSS JOIN LATERAL (

Initiates a lateral join, allowing the subquery to see 'u.id'.

WHERE o.user_id = u.id

References the outer table's column within the subquery scope.

FETCH FIRST 1 ROW ONLY

Limits the result set for each specific user to their highest order.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Lateral Derived Tables for Row-Wise Calculations

Related