Re: Selecting latest value - Mailing list pgsql-sql

From Tom Lane
Subject Re: Selecting latest value
Date
Msg-id 2678.1000998455@sss.pgh.pa.us
Whole thread Raw
In response to Re: Selecting latest value  (Patrik Kudo <kudo@partitur.se>)
List pgsql-sql
Patrik Kudo <kudo@partitur.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


pgsql-sql by date:

Previous
From: "Thurstan R. McDougle"
Date:
Subject: Re: table restruct...
Next
From: "Chris Ruprecht"
Date:
Subject: Re: Selecting latest value II