MySQL Get Latest Record In Group


Sometimes it can be useful to fetch the latest record for each product, or the latest record in each grouping.

This MySQL query returns the latest order placed by customer_id
SELECT t1.* 
FROM (
  SELECT o.timestamp, o.customer_id, o.info
  FROM placed_orders o
  WHERE 1
      # some where clause here
  ORDER BY o.timestamp DESC
) as t1
GROUP BY t1.customer_id;

PostGreSQL has support for this built in:
SELECT DISTINCT ON (customer_id) timestamp, customer_id, info
FROM placed_orders o
WHERE 1 # some where clause here
ORDER BY customer_id, timestamp DESC
http://stackoverflow.com/questions/435703/how-to-select-the-most-recent-set-of-dated-records-from-a-mysql-table/12625667

code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)