From went@hteamericas.com Mon Nov  20 17:09:03 2006
Received: from localhost (pluto.hub.org [200.46.204.4])
	by postgresql.org (Postfix) with ESMTP id C26FC9FA28E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:09:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024)
	with ESMTP id 66152-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:08:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 0C3C79FA285
	for <pgsql-sql@postgresql.org>; Mon, 20 Nov 2006 13:08:59 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from web.hteamericas.com (web.hteamericas.com [65.164.54.20])
	by svr4.postgresql.org (Postfix) with ESMTP id 5CF995AF02C
	for <pgsql-sql@postgresql.org>; Mon, 20 Nov 2006 17:08:59 +0000 (GMT)
Received: from miswen (pc111.hteamericas.com [192.168.10.111] (may be forged))
	by web.hteamericas.com (8.13.6/8.13.4) with SMTP id kAKH8vWT001005
	for <pgsql-sql@postgresql.org>; Mon, 20 Nov 2006 12:08:57 -0500
Message-ID: <00e001c70cc6$926292a0$6f0aa8c0@miswen>
From: "wen tseng" <went@hteamericas.com>
To: <pgsql-sql@postgresql.org>
Subject: How to find out top 3 records in each location
Date: Mon, 20 Nov 2006 12:09:00 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00DD_01C70C9C.A9724BE0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/214
X-Sequence-Number: 26866

This is a multi-part message in MIME format.

------=_NextPart_000_00DD_01C70C9C.A9724BE0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

On PostgreSQL, I have a table like this:

Item     Location    Sales
 A            X             10
 B            X              6
 C            Y              3
 D            Y              8
 E            Y              15
 F            Y             11

I'd like to find out top 3 items in each location and put those 3 items =
as colum values like this:

Location    Top1   Top2   Top3
  X              A        B
  Y             E         F        D

Since PostgreSQL doesn't support TOP, how can I do to get this result?
Any solution will be appreciated.

Thank you in advance.
------=_NextPart_000_00DD_01C70C9C.A9724BE0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>On PostgreSQL, </FONT><FONT =
face=3DArial size=3D2>I=20
have a table like this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Item&nbsp;&nbsp;&nbsp;&nbsp;=20
Location&nbsp;&nbsp;&nbsp; Sales</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>&nbsp;A&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
10</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2>&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;=20
X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;=20
6</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2>&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;=20
Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
&nbsp;3</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2>&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;=20
Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;=20
8</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2>&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;=20
Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;=20
15</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2>&nbsp;F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;11</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>I'd like to find out top 3 =
items in each=20
location and put those 3 items as colum values like this:</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>Location&nbsp;&nbsp;&nbsp; =

Top1&nbsp;&nbsp; Top2&nbsp;&nbsp; Top3</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>&nbsp;=20
X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;=20
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>&nbsp;=20
Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
=20
E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; D</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>Since PostgreSQL doesn't =
support=20
TOP,&nbsp;how can I do to get this result?</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>Any solution will be=20
appreciated.</FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2>Thank you in=20
advance.</FONT></DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_00DD_01C70C9C.A9724BE0--




From ajs@crankycanuck.ca Mon Nov  20 17:48:48 2006
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D23EF9FA2CB
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:48:48 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 80245-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:48:35 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id 62F209F9ED7
	for <pgsql-sql@postgresql.org>; Mon, 20 Nov 2006 13:48:35 -0400 (AST)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1GmDK3-0004A9-Rv
	for pgsql-sql@postgresql.org; Mon, 20 Nov 2006 12:52:47 -0500
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id 843944172; Mon, 20 Nov 2006 12:48:22 -0500 (EST)
Date: Mon, 20 Nov 2006 12:48:22 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: How to find out top 3 records in each location
Message-ID: <20061120174822.GB29317@phlogiston.dyndns.org>
References: <00e001c70cc6$926292a0$6f0aa8c0@miswen>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <00e001c70cc6$926292a0$6f0aa8c0@miswen>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/215
X-Sequence-Number: 26867

On Mon, Nov 20, 2006 at 12:09:00PM -0500, wen tseng wrote:
> 
> Since PostgreSQL doesn't support TOP, how can I do to get this result?
> Any solution will be appreciated.

Check out the crosstab stuff in contrib/; I think it does what you
want.

a

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
		--George Orwell



From rabroersma@yahoo.com Mon Nov  20 17:49:26 2006
Received: from localhost (pluto.hub.org [200.46.204.4])
	by postgresql.org (Postfix) with ESMTP id 07AEE9F9ED7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:49:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024)
	with ESMTP id 93421-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 20 Nov 2006 13:49:21 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from web31801.mail.mud.yahoo.com (web31801.mail.mud.yahoo.com
	[68.142.207.64]) by postgresql.org (Postfix) with SMTP id 9EB699FA219
	for <pgsql-sql@postgresql.org>; Mon, 20 Nov 2006 13:49:20 -0400 (AST)
Received: (qmail 25185 invoked by uid 60001); 20 Nov 2006 17:49:19 -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=2r0JBuD7VhjXl5OtiqUjXdp4L0DtFgDjy/ssvG2OG/tA4+4QwS05bPH4KHio0uK4WKgko3o3UE5gHrGMmuce/M3uDbjlIPLWV1I+1wGXWEbtc3kr9MdTfpTiGcTmBeGcjfqR9VyUA12HfqiJ11ya2b9/MsutSdm44IltNRq9mYk=
	;
X-YMail-OSG: 
 SdOABkgVM1kjPEQMENiFHHfZ_HBJMYjag8gW8ihmT2mi9Fo3.LmA3mpw9K5qlYU0C9KvOACW.z1fjIs3yMk98NyOrTOBDG22zOxmIDCKsyqoJUwefrKxpO4v8lw.BFeGPRNuTxCfujLvso_ffW.I5YEDR79jpsAn43DItYzY8XkCL3UFQm8a7R0vlX3F
Received: from [12.177.97.50] by web31801.mail.mud.yahoo.com via HTTP;
	Mon, 20 Nov 2006 09:49:19 PST
Date: Mon, 20 Nov 2006 09:49:19 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: How to find out top 3 records in each location
To: wen tseng <went@hteamericas.com>, pgsql-sql@postgresql.org
In-Reply-To: <00e001c70cc6$926292a0$6f0aa8c0@miswen>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <452822.24102.qm@web31801.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.262 tagged_above=0 required=5 tests=AWL, BAYES_00,
	DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS
X-Spam-Level: 
X-Archive-Number: 200611/216
X-Sequence-Number: 26868

> On PostgreSQL, I have a table like this:
> 
> Item     Location    Sales
>  A            X             10
>  B            X              6
>  C            Y              3
>  D            Y              8
>  E            Y              15
>  F            Y             11
> 
> I'd like to find out top 3 items in each location and put those 3 items as colum values like
> this:
> 
> Location    Top1   Top2   Top3
>   X              A        B
>   Y             E         F        D
> 
> Since PostgreSQL doesn't support TOP, how can I do to get this result?
> Any solution will be appreciated.


Here is what I came up with.  However, I am sure there maybe a better answer.

SELECT 
	A1.location,
	( select sales
	  from sales
	  where location = A1.location
	  order by sales desc 
	  limit 1
	) as TOP1,
	( select sales
	  from sales
	  where location = A1.location
	  order by sales desc 
	  limit 1 offset 1
	) as Top2,
	( select sales
	  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.



From rabroersma@yahoo.com Mon Nov  20 23:24:30 2006
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id C5C7C9F9F7B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <rabroersma@yahoo.com>
Subject: Re: How to find out top 3 records in each location
To: Richard Broersma Jr <rabroersma@yahoo.com>,
	wen tseng <went@hteamericas.com>, 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
>