pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect every first/last record of a partition?
2+ messages / 2 participants
[nested] [flat]
* Select every first/last record of a partition?
@ 2012-05-21 16:04 Andreas <maps.on@gmx.net>
2012-05-21 21:17 ` Re: Select every first/last record of a partition? Seth Gordon <sethg@ropine.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2012-05-21 16:04 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Select every first/last record of a partition?
2012-05-21 16:04 Select every first/last record of a partition? Andreas <maps.on@gmx.net>
@ 2012-05-21 21:17 ` Seth Gordon <sethg@ropine.com>
0 siblings, 0 replies; 2+ messages in thread
From: Seth Gordon @ 2012-05-21 21:17 UTC (permalink / raw)
To: pgsql-sql
I think this would work:
select distinct on (id) id, ts --and whatever other columns you want
from mytab
order by id, timestamp desc;
On Mon, May 21, 2012 at 12:04 PM, Andreas <maps.on@gmx.net> wrote:
> 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.
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-05-21 21:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-21 16:04 Select every first/last record of a partition? Andreas <maps.on@gmx.net>
2012-05-21 21:17 ` Seth Gordon <sethg@ropine.com>
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