Skip site navigation (1) Skip section navigation (2)

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

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Dave Crooke <dcrooke(at)gmail(dot)com>, Kevin Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov>, Michael Glaesemann <grzm(at)seespotcode(dot)net>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Picking out the most recent row using a time stamp column
Date: 2011-02-25 00:58:33
Message-ID: 4D66FEB9.6000501@peak6.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 02/24/2011 06:20 PM, Josh Berkus wrote:

> SELECT id_key, time_stamp, value
> FROM (
> 	SELECT id_key, time_stamp, value,
> 		row_number()
> 		OVER ( PARTITION BY id_key
> 			ORDER BY time_stamp DESC)
>   		as ranking
> 	FROM thetable
>        ) as filtered_table
> WHERE ranking = 1

Why did you use row_number instead of rank?

I am now curious how the speed compares though. I still think the 
DISTINCT ON will be faster, but it would be a great surprise.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2011-02-25 01:52:19
Subject: Re: Picking out the most recent row using a time stamp column
Previous:From: Josh BerkusDate: 2011-02-25 00:20:00
Subject: Re: Picking out the most recent row using a time stamp column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group