Brian Menke wrote:
Sorry about the last post, I hope this one comes over better.
This is a bit embarrassing because I think this should be a simple WHERE
clause, but it escapes me at the moment.
I have a table with data:
module_id email score date_time
1 [EMAIL PROTECTED] 8 5/27/2006 12:07 AM
1 [EMAIL PROTECTED] 9 5/27/2006 12:11 AM
1 [EMAIL PROTECTED] 9 5/27/2006 12:14 AM
1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM
2 [EMAIL PROTECTED] 8 5/24/2006 9:46 PM
1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM
2 [EMAIL PROTECTED] 7 5/27/2006 9:47 PM
3 [EMAIL PROTECTED] 7 5/27/2006 9:47 PM
3 [EMAIL PROTECTED] 8 5/27/2006 9:50 PM
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. You
can see that Kris and Robert have taken the same test several times. I just
want the latest results (even if the score was worse). I'm not sure how to
grab the latest module_id for each person? Ultimately, I want my result to
look like this
module_id email score date_time
1 [EMAIL PROTECTED] 9 5/27/2006 12:14 AM
1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM
2 [EMAIL PROTECTED] 8 5/24/2006 9:46 PM
1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM
2 [EMAIL PROTECTED] 7 5/27/2006 9:47 PM
3 [EMAIL PROTECTED] 8 5/27/2006 9:50 PM
I looked a few places on google, but they seemed to be suggesting using DESC
in the where clause and I didn't see how that was going to work? I also
looked at DISTNCT, GROUP BY and ORDER BY, which I think is the right
direction, but I can't seem to figure out how to piece them together.
Any ideas are greatly appreciated!
-Brian Menke
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>.
You should be able to adapt the examples there to your situation, but let us
know if you need help.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
