SQL join: selecting the last records in a one-to-many relationship

Question

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes?

Please use these table/column names in your answer:

customer: id, name

purchase: id, customer_id, item_id, date

And in more complicated situations, would it be (performance-wise) beneficial to denormalize the database by putting the last purchase into the customer table?

If the (purchase) id is guaranteed to be sorted by date, can the statements be simplified by using something like LIMIT 1?

Answer 1

This is an example of the greatest-n-per-group problem. This is how it should be solved:

SELECT c.*, p1.* FROM customer cJOIN purchase p1 ON (c.id = p1.customer_id)LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND(p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))WHERE p2.id IS NULL;

Explanation:

In the above Query if a row p1 is given, then there should be no row p2 with the same later date and customer. Row p1 will be the most recent purchase for that customer once you find that to be true.

For indexes, yo need to create the compound index in purchase over the columns (customer_id, date, id) and that allows the outer join to be done using the covering index.