|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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 |
|
#2
|
||||
|
||||
|
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/ |
|
#3
|
|||
|
|||
|
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!
|
|
#4
|
||||
|
||||
|
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 |
|
#5
|
|||
|
|||
|
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! |
|
#6
|
||||
|
||||
|
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 |
|
#7
|
|||
|
|||
|
It worked like a champ! Thanks so much for sticking with me on this!
|
|
#8
|
|||
|
|||
|
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? |
|
#9
|
||||
|
||||
|
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 ![]() |
|
#10
|
|||
|
|||
|
Your point is right on the money......... Sometimes I can just kick myself for my stupidity... Thanks again for all your help and patience!
|
![]() |
| Viewing: Dev Shed Forums > Databases > PostgreSQL Help > top n sql |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|