4 messages in com.mysql.lists.mysqlRe: Simple GROUP / ORDER problem| From | Sent On | Attachments |
|---|---|---|
| Lee Denny | 29 Jun 2005 05:00 | |
| Brent Baisley | 29 Jun 2005 05:32 | |
| Rhino | 29 Jun 2005 05:45 | |
| SGr...@unimin.com | 29 Jun 2005 06:49 |
| Subject: | Re: Simple GROUP / ORDER problem |
|---|---|
| From: | SGr...@unimin.com (SGr...@unimin.com) |
| Date: | 06/29/2005 06:49:44 AM |
| List: | com.mysql.lists.mysql |
"Lee Denny" <le...@quesit.com> 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)....