pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find out top 3 records in each location
4+ messages / 3 participants
[nested] [flat]
* How to find out top 3 records in each location
@ 2006-11-20 17:09 wen tseng <went@hteamericas.com>
0 siblings, 2 replies; 4+ messages in thread
From: wen tseng @ 2006-11-20 17:09 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find out top 3 records in each location
@ 2006-11-20 17:48 Andrew Sullivan <ajs@crankycanuck.ca>
parent: wen tseng <went@hteamericas.com>
1 sibling, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2006-11-20 17:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find out top 3 records in each location
@ 2006-11-20 17:49 Richard Broersma Jr <rabroersma@yahoo.com>
parent: wen tseng <went@hteamericas.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2006-11-20 17:49 UTC (permalink / raw)
To: wen tseng <went@hteamericas.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find out top 3 records in each location
@ 2006-11-20 17:57 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2006-11-20 17:57 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; wen tseng <went@hteamericas.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-20 17:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-20 17:09 How to find out top 3 records in each location wen tseng <went@hteamericas.com>
2006-11-20 17:48 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-11-20 17:49 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-20 17:57 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox