Re: Picking out the most recent row using a time stamp column

[Dave Crooke <dcrooke@xxxxxxxxx>]

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@xxxxxxxxxxxx> wrote:

Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> 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;

Dang! ÂI forgot the DESC in my post! ÂThanks for showing the
*correct* version.

-Kevin