Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C5C7C9F9F7B for ; Mon, 20 Nov 2006 19:24:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 50608-01 for ; Mon, 20 Nov 2006 23:24:25 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web31813.mail.mud.yahoo.com (web31813.mail.mud.yahoo.com [68.142.207.76]) by postgresql.org (Postfix) with SMTP id 59D679F9B32 for ; Mon, 20 Nov 2006 19:24:24 -0400 (AST) Received: (qmail 89020 invoked by uid 60001); 20 Nov 2006 17:57:41 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=3DiT/3XQL5t7m+yH9u4485A0g10/fxHYAP1vk0b8eAZStudc2Sr6D9iTp0p2+Q0/mHiW5bzGyclEvaQO1Xy9gTVRqZm0zeFCIfmQ8GNt+zyCwKf7hnjZ8nTZMDbjcQPtxMSdoyleCpfXlSdVxocAMJiir9Au92bxTTPMBe497To= ; X-YMail-OSG: BcQugxkVM1ns0rHne5GNVUIL3sDEn4a5dutU7jdB Received: from [12.177.97.50] by web31813.mail.mud.yahoo.com via HTTP; Mon, 20 Nov 2006 09:57:41 PST Date: Mon, 20 Nov 2006 09:57:41 -0800 (PST) From: Richard Broersma Jr Subject: Re: How to find out top 3 records in each location To: Richard Broersma Jr , wen tseng , pgsql-sql@postgresql.org In-Reply-To: <452822.24102.qm@web31801.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <713709.88122.qm@web31813.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.756 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200611/218 X-Sequence-Number: 26870 Oops, I made a mistake the sub-selects should be item not sales... > SELECT > A1.location, > ( select item ^^^^ > from sales > where location = A1.location > order by sales desc > limit 1 > ) as TOP1, > ( select item ^^^^ > from sales > where location = A1.location > order by sales desc > limit 1 offset 1 > ) as Top2, > ( select item ^^^^ > from sales > where location = A1.location > order by sales desc > limit 1 offset 2 > ) as Top3 > > > FROM > ( SELECT location > from sales > group by location > ) AS A1 > ; > > > Regards, > > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >