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

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 (AT) gmx (DOT) net> wrote:
--
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