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

by

This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow.

Here’s how I usually recommend solving it:

SELECT c.*, p1.*
FROM customer c
JOIN 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: given a row p1, there should be no row p2 with the same customer and a later date (or in the case of ties, a later id). When we find that to be true, then p1 is the most recent purchase for that customer.

Regarding indexes, I’d create a compound index in purchase over the columns (customer_id, date, id). That may allow the outer join to be done using a covering index. Be sure to test on your platform, because optimization is implementation-dependent. Use the features of your RDBMS to analyze the optimization plan. E.g. EXPLAIN on MySQL.

Some people use subqueries instead of the solution I show above, but I find my solution makes it easier to resolve ties.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. SQL update from one Table to another based on a ID match
  3. JOIN two SELECT statement results
  4. SQL SELECT from multiple tables
  5. How do I UPDATE from a SELECT in SQL Server?
  6. Get top 1 row of each group
  7. Explicit vs implicit SQL joins
  8. COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? [duplicate]
  9. SQL Inner-join with 3 tables?
  10. SQLite – How do you join tables from different databases?
  11. What’s the best way to join on the same table twice?
  12. Can we use join for two different database tables?
  13. Postgres unique constraint vs index
  14. How to select id with max date group by category in PostgreSQL?
  15. Call a set-returning function with an array argument multiple times
  16. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  17. How can I select from list of values in Oracle
  18. How do I select all the columns from a table, plus additional columns like ROWNUM?
  19. Select a Column in SQL not in Group By
  20. Best way to do nested case statement logic in SQL Server
  21. ORA – 00933 confusion with inner join and “as”
  22. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  23. SQL column reference “id” is ambiguous
  24. Venn Diagram for Natural Join
  25. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  26. Why do multiple-table joins produce duplicate rows?
  27. Select corresponding to row from the same table SQL Server
  28. IN vs. JOIN with large rowsets
  29. SQL join on multiple columns in same tables
  30. Two single-column indexes vs one two-column index in MySQL?

Leave a Comment