The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> PostgreSQL Help
|
top n sql
Discuss top n sql in the PostgreSQL Help forum on Dev Shed. top n sql PostgreSQL Help forum discussing administration, SQL syntax, or other PostgreSQL-related topics. PostgreSQL provides enterprise level database functionality at open source prices.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

April 11th, 2003, 07:43 AM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
top n sql
I am trying to write a select statement using Postgres to get the top 3 users in each problem category with the most problem incidents.
For example:
email problems:
mary 5
jason 3
amy 2
beth 1
riley 1
network problems:
bob 7
john 6
fred 4
howard 2
amy 1
sue 1
The results would produce:
email problems:
mary 5
jason 3
amy 2
network problems:
bob 7
john 6
fred 4
This contains the subselect that works so far to get me the count of problems by category sorted by count descending within each category. Now I need to extract out the top 3 from each category.
SELECT subtable.type,subtable.requestor,subtable.foo
FROM
(SELECT type as type,requestor as requestor,count(*) as foo
FROM incident
group by type,requestor
ORDER BY type,count(*) DESC) subtable
??????????????????????;
Any help would be much appreciated! Laura
|

April 11th, 2003, 07:59 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
i believe postgresql has LIMIT syntax, so
Code:
select type
, requestor
, problems
from incident X
where problems in
( select problems
from incident
where type = X.type
order by problems desc limit 3 )
order by type
, problems desc
rudy
http://r937.com/
|

April 22nd, 2003, 11:30 AM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Thanks for the feedback. I was hoping I would not have to use views since I have several problem categories and would like to have 'top n' be dynamic based on what the user enters, so a stored procedure would work best. I found that our version of Postgres (7.2) doesn't support set manipulation and has other limitations which prevent me to use functions. So I do in fact need to use the views as you suggested. Appreciate your taking the time!
|

April 22nd, 2003, 12:04 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
views? functions? i did not mention those
my solution uses a subquery, that is all
i do not have postresql to test on, but i'm pretty sure it supports subqueries
give my solution a try
rudy
|

April 22nd, 2003, 02:04 PM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Sorry for the view/function reference. I got some other suggestions from another source.
I took your subquery and with minor changes that were needed I ran the following which gave me an error in that an aggregate cannot be used in a where clause.
select type
, requestor
, count(*)
from incident X
where count(*) in
( select count(*)
from incident
where type = X.type
group by type,requestor
order by count(*) desc limit 3 )
group by type,requestor
order by type
, count(*) desc;
If you have any further insights I would be glad to hear them. Thanks!
|

April 22nd, 2003, 02:24 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
for that type of construction (your initial question made it look like "problems" was an actual column, not an aggregate), you need HAVING instead of WHERE
select type
, requestor
, count(*)
from incident X
group by type, requestor
HAVING count(*) in
( select count(*)
from incident
where type = X.type
group by requestor
order by count(*) desc limit 3 )
order by type
, count(*) desc;
i'm not sure if you can have a correlated subquery in the HAVING clause but it's certainly worth a try, please let me know if it works
rudy
|

April 22nd, 2003, 02:34 PM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
It worked like a champ! Thanks so much for sticking with me on this!
|

April 22nd, 2003, 02:39 PM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Ooops, one little problem. If there is more than one occurrence of the same count within a type, you will see more then 3 line items for a type, such as
network rachel 6
network bill 4
network sally 4
network dave 4
network mary 3
Any thoughts?
|

April 22nd, 2003, 02:55 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
my thoughts? yes, that is exactly the right result
otherwise, you are going to have to find some method of deciding which one of bill, sally, and dave should get kicked out
what do they do in the olympics? (french judges aside)
if bill and sally and dave tie for second place, does that mean one of them doesn't get a silver medal and mary doesn't get the bronze?
i'm sorry, but i think ties should be included
those are my thoughts

|

April 23rd, 2003, 07:02 AM
|
|
Junior Member
|
|
Join Date: Jun 2002
Posts: 13
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Your point is right on the money......... Sometimes I can just kick myself for my stupidity... Thanks again for all your help and patience!
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|