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=
	;
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>

> 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.