"Lee Denny" <[EMAIL PROTECTED]> wrote on 06/29/2005 08:00:49 AM:
> Hello, > > If got a simple sessions table basically holds a session id and datetime > field for last modification also a session type, so I can have several > records with the same session id, with different types and different > modification time. I want to get the latest modified record for any given > session, and I'm using > > SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC > > on this example data set : > session_id | type | date_modified > d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 > d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 > d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 > d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 > 09ebae82723018355559c519cc3bb0ca 2 2005-06-28 20:38:18 > > Although this returns the individual sessions in the correct order, the > group by is returning the earliest record for that session_id so I get: > > d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 > 09ebae82723018355559c519cc3bb0ca 2 2005-06-28 20:38:18 > > Rather than > > d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 > 09ebae82723018355559c519cc3bb0ca 2 2005-06-28 20:38:18 > > Which is what I want, I'm sure this is a misunderstanding on my part, but > does any one have any ideas? > > All the best, > > Lee > This is a very FAQ. It is the "group-wise maximum" problem and it is well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - In fact, this problem is so frequently asked, you are the second person in the last 24 hours to post it (different fields, of course, but same problem)....
