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

i believe postgresql has LIMIT syntax, so

rudy
http://r937.com/

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!

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

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!

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

It worked like a champ! Thanks so much for sticking with me on this!

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?

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

Your point is right on the money......... Sometimes I can just kick myself for my stupidity... Thanks again for all your help and patience!