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...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
