Hi back Carl van Tast wrote: > > Hi, Thurstan > > On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle" > <[EMAIL PROTECTED]> 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). ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
