Re: Selecting latest value II

Hi, Thurstan

On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle"
<trmcdougle@my-deja.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;

That's the reason I'm here: learning by helping :-)

Kind regardsCarl van Tast