Re: Selecting latest value

Patrik Kudo <kudo(at)partitur(dot)se> writes:
> create table (userid text, val integer, ts timestamp);
> This table holds multiple values for users, timestamped for history
> reasons.
>
> Now I need to fetch the latest val for each userid to insert into a new
> table (with about the same schema, except for uniqueness on userid).
> I belive this should be a trivial task, but I'm experience total lack of
> insight here...

This is what SELECT DISTINCT ON was invented for. I don't know any
comparably easy way to do it in standard SQL, but with DISTINCT ON
it's not hard:

SELECT DISTINCT ON (userid) userid, val, ts FROM table
ORDER BY userid, ts DESC;

See the DISTINCT ON example in the SELECT reference page for more info:
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-select.html

regards, tom lane