Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
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.