PostgreSQL - Joins in PostgreSQL
You want to list each customer with their two most recent orders using LATERAL join. Which query correctly achieves this?
You want to list each customer with their two most recent orders using LATERAL join. Which query correctly achieves this?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We want two most recent orders per customer, so order by order_date DESC and limit 2 in subquery.Step 2: Check each option
SELECT c.name, o.order_id FROM customers c JOIN LATERAL (SELECT order_id FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 2) o ON true; uses JOIN LATERAL with correct ordering and limit, joining on true to include all customers with orders. SELECT c.name, o.order_id FROM customers c JOIN orders o ON o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 2; limits total rows, not per customer. SELECT c.name, o.order_id FROM customers c CROSS JOIN LATERAL orders o WHERE o.customer_id = c.id LIMIT 2; uses CROSS JOIN LATERAL incorrectly without subquery. SELECT c.name, o.order_id FROM customers c LEFT JOIN LATERAL (SELECT order_id FROM orders WHERE customer_id = c.id ORDER BY order_date ASC LIMIT 2) o ON true; orders ASC, which gives oldest orders, not recent.Final Answer:
SELECT c.name, o.order_id FROM customers c JOIN LATERAL (SELECT order_id FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 2) o ON true; -> Option AQuick Check:
LATERAL with ORDER BY DESC and LIMIT 2 per customer [OK]
Quick Trick: Use LATERAL with ORDER BY DESC and LIMIT for top N per row [OK]
Common Mistakes:
- Limiting total rows instead of per customer
- Ordering ascending instead of descending
- Using CROSS JOIN LATERAL without subquery
Master "Joins in PostgreSQL" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes