From: Thurstan R. McDougle <trmcdougle@my-deja.com>
To: pgsql-sql@postgresql.org
Subject: Re: Selecting latest value II
Date: Fri, 21 Sep 2001 17:31:15 +0100
Message-ID: <3BAB6B53.9FBE01C1@my-deja.com> (raw)
References: <200109201402.QAA25852@rodos>
<3BAA19B6.84FAFDB6@my-deja.com>
<90hkqtoul1cnb9pqpn47i7544j9oik9s3d@4ax.com>
Hi back
Carl van Tast wrote:
>
> 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;
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: trmcdougle@my-deja.com
Subject: Re: Selecting latest value II
In-Reply-To: <3BAB6B53.9FBE01C1@my-deja.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox