Using Distinct ON to return newest order for each customer   

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?

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;