How to retrieve the row which has a maximum value in a result of GROUP BY ?

[pankajit09]

Hello,

I am using a GROUP BY in a query as follows -->

SELECT * FROM log GROUP BY name ORDER BY name,arrive,id desc

I want the rows which have the maximum id in the result of each "group by".

Please tell me how to do that ?

[pankajit09]

I think this might work -->

SELECT max(id),arrive,name FROM log group by name order by name,arrive

[ronverdonk]

That will not work. I show you an example:
Table zz:

+----+------------+------+
| id | arrival    | name |
+----+------------+------+
|  1 | 2006-12-31 | John |
|  2 | 2007-01-01 | John |
|  3 | 2007-01-12 | John |
|  4 | 2006-12-21 | Pete |
|  5 | 2007-01-10 | Pete |
|  6 | 2006-01-12 | Pete |
|  7 | 2006-06-16 | Mark |
+----+------------+------+

Executing your statement

SELECT max(id),arrival,name FROM zz 
    GROUP BY name ORDER BY name,arrival;

produces the following result:

+---------+------------+------+
| max(id) | arrival    | name |
+---------+------------+------+
|       3 | 2006-12-31 | John |
|       7 | 2006-06-16 | Mark |
|       6 | 2006-12-21 | Pete |
+---------+------------+------+

There is no connection between the max(id) and the row content. MAX() is only executed AFTER a row has been read.
So, for your single statement solution you must use a subquery in the WHERE clause that returns the maximum id value per name, as in:

SELECT id as highest,arrival,name FROM zz 
    WHERE id IN (SELECT MAX(id) FROM zz GROUP BY name);

That statement produces the required result:

+---------+------------+------+
| highest | arrival    | name |
+---------+------------+------+
|       3 | 2007-01-12 | John |
|       6 | 2006-01-12 | Pete |
|       7 | 2006-06-16 | Mark |
+---------+------------+------+

Ronald :cool:

[pankajit09]

I must say you are brilliant Ronald.

You are a good observer.

This saved me of a major bug in my project.

I have no words to thank you.

My best wishes to you .

[pankajit09]

But why you used "highest" in the Query above ?

[pankajit09]

ok I got that is just for renaming the column.

[ronverdonk]

Glad to be of service.

And yes, the column name 'highest' was just to illustrate that it was actually the highest id that was shown.

Ronald :cool:

[pankajit09]

But Ronald my actual query is like this -->

SELECT id,arrivedt,name,startdt,expiredt FROM act_log WHERE id IN(SELECT MAX(id) FROM act_log GROUP BY name) AND keywords REGEXP '".$keywords[0]."' OR keywords REGEXP'".$keywords[1]."' OR keywords REGEXP'".$keywords[2]."' AND csol='$csolnum'
ORDER BY name

Its not working.
Please tell me why ?

[pankajit09]

ok I got .

we have to put the OR conditions in bracket.

for eg:-

SELECT * FROM Persons WHERE
(FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson'