Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    select 3 results for each category

    Hi

    I need select three groups (ordered by members DESC) for each category...

    But... i am not sure how.

    I could do something like:

    PHP Code:
    SELECT  (
        
    SELECT for first category,
        (
        
    SELECT for second category,
        (
        
    SELECT for third category 
    etc... there can be a lot of categories so absolutly bad idea.

    I could do something like:

    PHP Code:
    SELECT FROM `groupsGROUP BY category ORDER BY members 
    But i dont know how to rewrite it for my purposes.

    If someone could help i would be very gratefull.

    Here is the sqlfiddlle i wrote for you:

    http://sqlfiddle.com/#!2/661b5/2/0

    So the result should be something like:

    Category1
    - first group with the most members
    - second group..
    -third group...

    Category2
    - first group...
    -second group
    -third group...

    Category 3
    ...
    Last edited by Trki; 09-20-2013 at 07:05 AM.

  • #2
    New Coder
    Join Date
    May 2012
    Posts
    30
    Thanks
    10
    Thanked 1 Time in 1 Post
    do you want to return 3 groups who have more members ?

  • #3
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Azeem Hassni View Post
    do you want to return 3 groups who have more members ?
    At the bottom of my question is how i need the result to be...

    I need select three groups (ordered by members DESC) for each category...

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    No good way to do this other than a UNION.

    Code:
    ( SELECT 1 AS ordering, category, count(*) AS memberCount 
      FROM table WHERE category='ABC' ORDER BY memberCount DESC LIMIT 3 )
    UNION 
    ( SELECT 2 AS ordering, category, count(*) AS memberCount
      FROM table WHERE category='DEF' ORDER BY memberCount DESC LIMIT 3 )
    UNION 
    ( SELECT 3 AS ordering, category, count(*) AS memberCount 
      FROM table WHERE category='GHI' ORDER BY memberCount DESC LIMIT 3 )
    ORDER BY ordering ASC, memberCount DESC
    Notice that the parentheses around each SELECT are needed in MySQL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    Now, you said you had a lot of categories to do.

    There *IS* another way to do this, but it's really hairy and complex.

    I would stick with the above scheme unless you have more than, say, 10 or 12 categories to do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Now, you said you had a lot of categories to do.

    There *IS* another way to do this, but it's really hairy and complex.

    I would stick with the above scheme unless you have more than, say, 10 or 12 categories to do.
    Hm... yea my syntax was not correct it was just for purposes of example.

    Ah okay than i will do it this way luckly for me i have only 9 categories but i dont like this way because when we add a new category i have to add new select for this query... dont know what i would do if creating category was possible by users.

    Thx anyway supermaster coder hh (marked as resolved)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •