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