Man, this took me two hours to figure out, but in the end, this worked! SELECT module_id, email, score, max(date_time) FROM completed_modules GROUP by module_id, email
module_id email score max(date_time) 1 [EMAIL PROTECTED] 8 5/27/2006 12:14 AM 1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM 1 [EMAIL PROTECTED] 8 5/27/2006 9:46 PM 2 [EMAIL PROTECTED] 7 5/28/2006 1:04 AM 2 [EMAIL PROTECTED] 8 5/24/2006 9:46 PM 2 [EMAIL PROTECTED] 7 5/27/2006 9:47 PM 3 [EMAIL PROTECTED] 8 5/28/2006 1:04 AM 3 [EMAIL PROTECTED] 7 5/27/2006 9:50 PM The real key was to group by two things module_id and email. Everything else was only producing one of each... which I didn't understand how it worked and was driving me crazy. Seems simple now but... yikes! Thanks for everyone's help on this! -Brian -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Saturday, May 27, 2006 9:53 PM To: Brian Menke Cc: mysql@lists.mysql.com Subject: Re: Get the record with the latest date 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.htm l>. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
