Re: Select every first/last record of a partition?

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(dot)on(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql