Select every first/last record of a partition?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Select every first/last record of a partition?
Date: 2012-05-21 16:04:55
Message-ID: 4FBA67A7.4010302@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

suppose a table that has records with some ID and a timestamp.

id, ts
3, 2012/01/03
5, 2012/01/05
7, 2012/01/07
3, 2012/02/03
3, 2012/01/05
5, 2012/03/01
7, 2012/04/04

to fetch every last row of those IDs I do:

select id, ts
from ( select id, ts, row_number() over ( partition by id order
by ts desc ) as nr from mytab ) as x
where nr = 1

Is there a another way without a subselect?
There might be more columns so the window-functions first/last won't help.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Seth Gordon 2012-05-21 21:17:44 Re: Select every first/last record of a partition?
Previous Message Mario Dankoor 2012-05-21 11:57:21 Re: master/detail