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, '[EMAIL PROTECTED]', 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 | [EMAIL PROTECTED] | 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
