Re: Selecting latest value II

Hi back

Carl van Tast wrote:
>
> Hi, Thurstan
>
> On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle"
> <trmcdougle(at)my-deja(dot)com> 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).