Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8KF89e01761 for ; Thu, 20 Sep 2001 11:08:09 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f8KF7ZK02681; Thu, 20 Sep 2001 11:07:35 -0400 (EDT) To: Patrik Kudo cc: Haller Christoph , pgsql-sql@postgresql.org Subject: Re: Selecting latest value In-reply-to: References: Comments: In-reply-to Patrik Kudo message dated "Thu, 20 Sep 2001 15:57:42 +0200" Date: Thu, 20 Sep 2001 11:07:35 -0400 Message-ID: <2678.1000998455@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200109/178 X-Sequence-Number: 4858 Patrik Kudo 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