René Fournier <[EMAIL PROTECTED]> wrote on 06/02/2005 02:53:51 PM: > I'm having a really hard time selecting rows from a table in one SELECT > statement. I can do it in two SELECTS, but it seems I should be able to > do it in one. > > TRIPS > > id date person_id cost > --------------------------------------------------------------- > 1 2005-01-01 2 500 > 2 2005-01-05 1 400 > 3 2005-01-12 4 350 > 4 2005-01-15 3 175 > 5 2005-01-17 2 385 > 6 2005-01-25 2 200 > 7 2005-02-03 3 600 > 8 2005-02-08 1 580 > 9 2005-02-20 4 320 > > PERSONS > > id name > ------------------------- > 1 john > 2 jane > 3 mike > 4 mary > 5 henry > > > Okay, I want to select from Trips the most recent trip for each person. > As you can see, some of the Persons have travelled more than once, but > I only want the last trip each one made. Also, not everyone in Persons > has made a trip (Henry). > > Here's the output I'm looking for: > > 2005-02-20 4 320 mary > 2005-02-08 1 580 john > 2005-02-03 3 600 mike > 2005-01-25 2 200 jane > > > I've written and re-written my SELECT queries numerous times, but > can't seem to retrieve just one, most-recent trip/person. Any ideas? > > ...Rene >
This is a VERY FAQ. It is a variant on the Groupwize maximum problem well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Basically you need to determine the max(trips.date) for each person_id then use that list (in combination with the person table) to create the report you wanted in the first place. The article shows 3 ways to make it happen: save your list into a temporar table, generate your list as the result of a subquery, or use the max-concat hack. If you prefer, the same article is also available in French, German, Japanese, Portuguese, and Russian. Just click on the appropriate link to the side. If you have tried this and still can't make it work, please come back with your query and I am sure someone will be very happy to help. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
