Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to find out top 3 records in each location

How to find out top 3 records in each location

From
"wen tseng"
Date:
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.

Re: How to find out top 3 records in each location

From
Andrew Sullivan
Date:
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

Re: How to find out top 3 records in each location

From
Richard Broersma Jr
Date:
> 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,(
selectsales  from sales  where location = A1.location  order by sales desc   limit 1 offset 1) as Top2,( select sales
fromsales  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.

Re: How to find out top 3 records in each location

From
Richard Broersma Jr
Date:
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
>