P.S. I noticed inadvertently (by making a typo ;-) that not all of the columns in the DISTINCT ON are required to be part of the output, in which case it appears to reduce the DISTINCT ON to the columns that are represented .... in my real world situation, "id_key" is actually composed of 3 columns, and I made a typo like the following (in which I've tweaked the spacing to highlight the missing comma:
select distinct on (a, b, c)a, b c, time_stamp, valuefrom dataorder by a, b, c, time_stamp desc;The output produced is the same as this query:
select distinct on (a, b)a, b, time_stamp, valuefrom dataorder by a, b, time_stamp desc;Not sure if this is considered a parser bug or not, but it feels slightly odd not to get an error.
PG 8.4.7 installed from Ubuntu 10.04's 64-bit build.
Cheers
Dave
On Thu, Feb 24, 2011 at 5:38 PM, Dave Crooke
<dcrooke@gmail.com> wrote:
Thanks to all .... I had a tickling feeling at the back of my mind that there was a neater answer here. For the record, times (all from in-memory cached data, averaged over a bunch of runs):
Dependent subquery = 117.9 seconds
Join to temp table = 2.7 sec
DISTINCT ON = 2.7 sec
So the DISTINCT ON may not be quicker, but it sure is tidier.
Cheers
Dave On Thu, Feb 24, 2011 at 2:24 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Michael Glaesemann <
grzm@seespotcode.net> wrote:
> SELECT DISTINCT ON (data.id_key)
> data.id_key, data.time_stamp, data.value
> FROM data
> ORDER BY data.id_key, data.time_stamp DESC;
*correct* version.
-Kevin