Thread: ids from grouped rows

Lets say i do this:

SELECT name, MAX(age)
FROM Person
GROUP BY name

This will group people who have the same name together, and return the
highest age for each group. I want to be able to find the id for the
person with the highest age in each group  -

Ideally, it would be something like this

SELECT name, MAX(age), id_for_row_with_max_age
FROM Person
GROUP BY name

Anyone know how to do this?

Lindsay

On Wed, 2005-07-20 at 08:46 -0400, Lindsay wrote:

> SELECT name, MAX(age), id_for_row_with_max_age
> FROM Person
> GROUP BY name

how about: select distinct on (name) name, age, id         from person        order by name, age desc;



gnari

Lindsay schrieb:
> Lets say i do this:
> 
> SELECT name, MAX(age)
> FROM Person
> GROUP BY name
> 
> This will group people who have the same name together, and return the
> highest age for each group. I want to be able to find the id for the
> person with the highest age in each group  -
> 
> Ideally, it would be something like this 
> 
> SELECT name, MAX(age), id_for_row_with_max_age
> FROM Person
> GROUP BY name

SELECT name, age, id FROM Person RIGHT JOIN (SELECT name, MAX(age) AS m
FROM  Person GROUP BY name) AS t ON (Person.name=t.name AND Person.age=t.m)

Stefan