Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Pankaj Yadav_1
Frequent Advisor

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

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 ?
13 REPLIES
Frank de Vries
Respected Contributor

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

Hi Pankaj

First of all you need to use a group by function in the select.

Valid functions in a GROUP BY statement are count(), sum(), min(), max() , avg() etc..

In your case:

SELECT max(id), arrive, name FROM log
GROUP BY arrive, name
name ORDER BY name,arrive,id desc

You can also then cascade the query by
doing a select on a select, in case you get
multiple rows and you only want the most or least for example

Thus:

Select max(alias)from
(SELECT max(id) alias , arrive, name FROM log
GROUP BY arrive, name
name ORDER BY name,arrive,id desc)

etc .. etc..
If you need to know more let me know:)

Look before you leap
Frank de Vries
Respected Contributor

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

Copy and past did work well, one name to many in previous answer:

SELECT max(id), arrive, name FROM log
GROUP BY arrive, name
ORDER BY name,arrive,id desc
Look before you leap
Pankaj Yadav_1
Frequent Advisor

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

I am using this -->

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


Its working fine.

do u find any problem ?
Frank de Vries
Respected Contributor

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

That looks perfect !


Look before you leap
Pankaj Yadav_1
Frequent Advisor

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

What points shall I give for the above statement ?
Pankaj Yadav_1
Frequent Advisor

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

Hey Frank the solution is this -->

SELECT id as highest,arrive,start,expire,name FROM log where id in(select max(id) from log group by name) order by name
Frank de Vries
Respected Contributor

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

Well Done Pankaj,
it appears you have improved from the basic input I gave you. Well done.

I translated your script to a situation
with for instance objects and it works well:)

SELECT object_id as highest,object_name ,object_type,created FROM dba_objects
where object_id in (select max(object_id) from dba_objects group by object_type)
order by object_id


I thank you .
Cheers :)

Look before you leap
Pankaj Yadav_1
Frequent Advisor

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

Well Frank I don't want to take credit for that becoz the bug in the query was removed by a person in another forum.

My doubt is that why "highest" is used in the above query ?
Hein van den Heuvel
Honored Contributor
Solution

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

>> SELECT object_id as highest

That just sets a name on tyhe column header.
It is equivalent to:

>> SELECT object_id "highest"...

Hein.
Pankaj Yadav_1
Frequent Advisor

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

But has it got any other significance apart from renaming "object_id" ?
spex
Honored Contributor

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

No.
Pankaj Yadav_1
Frequent Advisor

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

But my actual query is not working -->

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

Please tell me why its not working ?
Pankaj Yadav_1
Frequent Advisor

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

My MySQL server is old version so it doesn't support the complex query above.

UI have upgraded my server now.