Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 855ED133798E for ; Thu, 24 Feb 2011 20:58:49 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 14419-03 for ; Fri, 25 Feb 2011 00:58:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.peak6.com (smtp.optionshouse.com [38.98.137.6]) by mail.postgresql.org (Postfix) with ESMTP id 90C57133616B for ; Thu, 24 Feb 2011 20:58:36 -0400 (AST) X-AuditID: 0a64cb1e-b7c55ae000000bfb-ed-4d66feba84a6 Message-ID: <4D66FEB9.6000501@peak6.com> Date: Thu, 24 Feb 2011 18:58:33 -0600 From: Shaun Thomas Reply-To: sthomas@peak6.com Organization: Peak6 User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1 MIME-Version: 1.0 To: Josh Berkus CC: Dave Crooke , Kevin Grittner , Michael Glaesemann , "pgsql-performance@postgresql.org" Subject: Re: Picking out the most recent row using a time stamp column References: <08B3368E-E623-4E9C-83F5-AB05B471AF4C@seespotcode.net> <4D666A0F020000250003AFCE@gw.wicourts.gov> <4D66F5B0.5080502@agliodbs.com> In-Reply-To: <4D66F5B0.5080502@agliodbs.com> Content-Type: text/plain; charset="UTF-8"; format=flowed content-transfer-encoding: 7bit X-Brightmail-Tracker: AAAAAQAAAZE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201102/489 X-Sequence-Number: 42627 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@peak6.com ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email