Question
If you have say a set of orders from customers and for each customer you wanted to return the details of the last order for each customer, how would you do it?
Answer
In databases that don't support the handy SQL DISTINCT ON or equivalent clause, you'd have to resort to doing subselects and MAXes as we described in SQL Cheat Sheet: Query By Example - Part 2. If you are in PostgreSQL however you can use the much simpler to write DISTINCT ON construct - like so
SELECT DISTINCT ON (c.customer_id)
c.customer_id, c.customer_name, o.order_date, o.order_amount, o.order_id
FROM customers c LEFT JOIN orders O ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date DESC, o.order_id DESC;