Обсуждение: Select every first/last record of a partition?

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.

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