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

[Pankaj Yadav_1]

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 ?

[Frank de Vries]

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:)

[Frank de Vries]

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

[Pankaj Yadav_1]

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]

That looks perfect !

[Pankaj Yadav_1]

What points shall I give for the above statement ?

[Pankaj Yadav_1]

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]

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 :)

[Pankaj Yadav_1]

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 · Accepted solution]

>> SELECT object_id as highest

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

>> SELECT object_id "highest"...

Hein.