X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6AAB853FC8 for ; Sat, 16 Apr 2005 05:10:36 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07377-01 for ; Sat, 16 Apr 2005 08:10:34 +0000 (GMT) Received: from mta10-winn.mailhost.ntl.com (smtpout18.mailhost.ntl.com [212.250.162.18]) by svr1.postgresql.org (Postfix) with ESMTP id 4AF6353FD3 for ; Sat, 16 Apr 2005 05:10:32 -0300 (ADT) Received: from aamta02-winn.mailhost.ntl.com ([212.250.162.8]) by mta10-winn.mailhost.ntl.com with ESMTP id <20050416081033.IVJC9557.mta10-winn.mailhost.ntl.com@aamta02-winn.mailhost.ntl.com>; Sat, 16 Apr 2005 09:10:33 +0100 Received: from Wilbur ([81.96.74.213]) by aamta02-winn.mailhost.ntl.com with SMTP id <20050416081032.HHLZ1289.aamta02-winn.mailhost.ntl.com@Wilbur>; Sat, 16 Apr 2005 09:10:32 +0100 Message-ID: <004a01c5425b$c6b4c500$0600a8c0@Wilbur> From: "Julian Scarfe" To: "Alex" , References: <425FAC67.2050203@meerkatsoft.com> Subject: Re: SQL Question Date: Sat, 16 Apr 2005 09:10:37 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.102 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/684 X-Sequence-Number: 76597 From: "Alex" > - How can i select only the newest record for each ProdId ? > > 100 | 2005-04-01 > 200 | 2005-04-01 > DISTINCT ON was made for this and on the similar tables I have performs rather more efficiently than using a subquery. select distinct on (ProdId) ProdId , LastUpdate from produpdate order by ProdId , LastUpdate desc; > - How can i select to retrieve the last 2 dates in record > > 100 | 2005-04-01 | 2005-03-01 > 200 | 2005-04-01 | 2005-03-01 To get the previous one, my first thought is something like: select distinct on (ProdId) ProdId , LastUpdate from produpdate p1 where LastUpdate <> ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe