List:General Discussion« Previous MessageNext Message »
From:Kevin F. O'Riordan Date:May 28 2006 10:54pm
Subject:Re: Get the record with the latest date
View as plain text  
Hi Brian,

> Man, this took me two hours to figure out, but in the end, this worked!

> SELECT  ... max(date_time) ...

It /appeared/ to work, but with different test data you should see
that it isn't guaranteed to.  Try inserting:

  INSERT INTO completed_modules
    (module_id, email, score, date_time)
    VALUES (1, 'kris@innovtechxxcom', 5, '2006-05-29 11:11:00');

When I insert this line, then run your SELECT query, I get

  +-----------+-----------------------+-------+---------------------+
  | module_id | email                 | score | max(date_time)      |
  +-----------+-----------------------+-------+---------------------+
  |         1 | kris@innovtechxxcom   |     8 | 2006-05-29 11:11:00 |

  [remaining results snipped]

The 'max(date_time)' is as you're looking for; the 'score' is not.

Your use of 'max(date_time)' will select the most recent value for
'date_time', but the value selected for 'score' is independent of
this.

To solve your original problem correctly:

> > I'm trying to get a list of each persons completed test
> > (module_id), and the score, but only for the latest test result
> > (date_time), not all of them.  ...  I just want the latest results
> > (even if the score was worse).

You'll have to use Michael's suggestion:

> For each email-module_id combination, you want the row with the
> latest (maximum) date.  This is actually a FAQ, with solutions in
> the manual
>
> <http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html>.

good luck,
Kevin
Thread
Get the record with the latest dateBrian Menke28 May
  • Re: Get the record with the latest dateMichael Stassen28 May
    • RE: Get the record with the latest dateBrian Menke28 May
      • Re: Get the record with the latest dateKevin F. O'Riordan29 May