There may well be a way to do what you want but I'd like to make a brief point then let other people with more time give you the answer you want.
Simply put, GROUP BY is NOT intended to give you a specific record out of a group the way that you are trying to do. It's job is to do SUMMARIZATION of a group of records. For example, a typical use of GROUP BY would be something like this: select deptno, sum(salary) from employee group by deptno Given a table of employee rows with one row for each employee and where each row contained at least the employee's department number and salary, this query would group all employees together based on their department number then compute and report the total salary paid to each department. Therefore, GROUP BY is telling the database to add up the salaries for each of the people that belong to the first department and report it, then do the second department, then the third department, and so on until all departments have been handled. The query does NOT return the rows of SPECIFIC employees. The query you want, on the other hand, is trying to choose a particular one of the rows in your group and is not doing summarization at all. In short, I don't think you want to be doing a GROUP BY at all. Instead, I think you want to use ORDER BY and perhaps some WHERE clauses to do what you want to do. Just something for you to think about.... Rhino ----- Original Message ----- From: "Lee Denny" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Wednesday, June 29, 2005 8:00 AM Subject: Simple GROUP / ORDER problem > 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.8.5/32 - Release Date: 27/06/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.6/33 - Release Date: 28/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
