Query to get customers and the items from their last purchase

Let's say you want to get a list of all customers in your database and also include all items from their last purchase. How would you do it?

Here's one way to do it using a sub query.

SELECT customers.firstname, customers.lastname, purchases.createdat, items.name
FROM customers
INNER JOIN
(
  SELECT customerid, MAX(createdat) AS createdat
  FROM purchases
  GROUP BY customerid
) AS lastpurchase ON customers.id = lastpurchase.customerid
INNER JOIN purchases ON lastpurchase.customerid = purchases.customerid AND lastpurchase.createdat = purchases.createdat
INNER JOIN items ON purchases.id = items.purchaseid

I like this solution because the query is easy to read and understand. However, there are other ways to accomplish the same thing. One improvement was suggested by Jacob Bogers on Twitter. You can speed up the query by replacing the sub query portion of the query above with the following SQL instead.

SELECT customerid, createdat
FROM purchases a
WHERE NOT EXISTS
(
  SELECT 1
  FROM purchases b
  WHERE a.customerid = b.customerid AND a.createdat < b.createdat
)

Some other considerations.

• If you want to include customers without purchases, just replace all INNER JOIN statements with LEFT OUTER JOIN statements instead.
• If you use auto-incrementing primary keys in the purchases table you can call MAX on that column instead of the createdat column.

Home