pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

ids from grouped rows
3+ messages / 3 participants
[nested] [flat]

* ids from grouped rows
@ 2005-07-20 12:46  Lindsay <hellolindsay@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Lindsay @ 2005-07-20 12:46 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ids from grouped rows
@ 2005-07-20 14:09  Weinzierl Stefan <Stefan@Weinzierl-Stefan.de>
  parent: Lindsay <hellolindsay@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Weinzierl Stefan @ 2005-07-20 14:09 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: ids from grouped rows
@ 2005-07-20 14:50  Ragnar Hafstað <gnari@simnet.is>
  parent: Lindsay <hellolindsay@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Ragnar Hafstað @ 2005-07-20 14:50 UTC (permalink / raw)
  To: Lindsay <hellolindsay@gmail.com>; +Cc: pgsql-sql

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

 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-07-20 14:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-20 12:46 ids from grouped rows Lindsay <hellolindsay@gmail.com>
2005-07-20 14:09 ` Weinzierl Stefan <Stefan@Weinzierl-Stefan.de>
2005-07-20 14:50 ` Ragnar Hafstað <gnari@simnet.is>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox