Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8LGXge49036 for ; Fri, 21 Sep 2001 12:33:42 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8LGW2o41440 for pgsql-sql@postgresql.org; Fri, 21 Sep 2001 12:32:02 -0400 (EDT) (envelope-from news) From: "Thurstan R. McDougle" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Selecting latest value II Date: Fri, 21 Sep 2001 17:31:15 +0100 Message-ID: <3BAB6B53.9FBE01C1@my-deja.com> References: <200109201402.QAA25852@rodos> <3BAA19B6.84FAFDB6@my-deja.com> <90hkqtoul1cnb9pqpn47i7544j9oik9s3d@4ax.com> X-NNTP-Posting-Host: no-dns-yet.demon.co.uk:193.195.66.176 X-Complaints-To: abuse@demon.net X-Mailer: Mozilla 4.77 [en] (Win98; U) X-Accept-Language: en,pdf MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 49 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/186 X-Sequence-Number: 4868 Hi back Carl van Tast wrote: > > Hi, Thurstan > > On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle" > wrote: > > > [...] > >Carl van Tast had 2 good methods as follows > > > >SELECT userid, val > >FROM tbl > >WHERE NOT EXISTS (SELECT * FROM tbl AS t2 > > WHERE tbl.userid=t2.userid AND t2.ts > tbl.ts); > > > >or > > > >SELECT tbl.userid, tbl.val > >FROM tbl > > INNER JOIN > > (SELECT userid, max(ts) AS maxts > > FROM tbl > > GROUP BY userid) AS t2 > > ON (tbl.userid=t2.userid AND tbl.ts=t2.maxts); > > ... although I like Tom Lane's even better. This one should outperform > all others, especially my first one: > > > SELECT DISTINCT ON (userid) userid, val, ts FROM table > > ORDER BY userid, ts DESC; Indeed. I was looking for a 1 pass method but had not found one. I had thought of DISTINCT, but not DISTINCT ON as it I have not used it yet... Even though I had just been looking at the code for the DISTINCT/DISTINCT ON processing! > > That's the reason I'm here: learning by helping :-) Well, I think of helping more as paying my dues for the help received. > > Kind regards > Carl van Tast -- This is the identity that I use for NewsGroups. Email to this will just sit there. If you wish to email me replace the domain with knightpiesold . co . uk (no spaces).