Thread: top n sql

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep Power
    0

    Question 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    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/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep 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!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    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
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep 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!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    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
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep Power
    0
    It worked like a champ! Thanks so much for sticking with me on this!
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep 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?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    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

  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    13
    Rep 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!

IMN logo majestic logo threadwatch logo seochat tools logo