#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0

    Grab results with only the same top count number


    What I'm trying to do is count the number of times a a column record is present and then display the top result. With this normally I would do "Limit 1,0" however in this instance I don't want to limit it to a single result as there might be times where there could be several records sharing the same top count number. For example:
    Code:
    ----------------
    | Name | Count |
    ----------------
    | Usr1 |   4   |
    | Usr2 |   2   |
    | Usr3 |   4   |
    | Usr4 |   3   |
    ----------------
    As you can see if I were to order the results by count and then by name I would get Usr1, Usr3, Usr4, Usr2 in that order. If I added a limit to 1,0 then I would get just Usr1. I could put a limit of 2 but how would I know there would be 2 users?

    If I had 100 different users and there were 10 users that shared the same top count how would I got about grabbing only those 10 users that share the same top count number? Also is it possible to grab the lowest count number?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Assign them a rank then add a clause 'allowing' only those of a given rank, or less.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. Old Fart
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Chicago
    Posts
    106
    Rep Power
    4
    You could order the results by count and then by name and set limit to some arbitrarily large number. Then ignore all remaining returned results when the count changes.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0
    That's what I've been trying to do but couldn't work it out, as soon as the count changes I don't know how to suddenly cut out the rest. My current SQL is like this:
    PHP Code:
    SELECT
        site_games_list
    .box_art AS image,
        
    site_games_list.name AS game,
        
    COUNT(site_gift_transactions.game) AS top
    FROM site_gift_transactions
    INNER
        JOIN site_games_list
        ON site_games_list
    .id site_gift_transactions.game
    WHERE moderated 
    1
    GROUP BY game
    ORDER BY top DESC
    game
    LIMIT 1
    ,
    The tables are as follows (in regards to the relevant columns used):
    Code:
    --------------------------
    | site_gift_transactions |
    --------------------------
    |    id     |   int(10)  |
    | moderated |   int(1)   |
    |   game    |   int(6)   |
    --------------------------
    
    --------------------------
    |     site_games_list    |
    --------------------------
    |    id     |   int(6)   |
    |   name    |varchar(100)|
    |  box_art  |varchar(100)|
    --------------------------
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by Varsh
    ...I don't know how to suddenly cut out the rest.
    if you're going to use richpri's method, you have to use a number higher than 1 in your LIMIT clause, try something like 15, and then check for the change in count using your application language (php or whatever)

    better would be to use rank as simon suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0
    Yeah I've been trying the rank method without too much luck, like you said I will need to use the rank method. I've got a few problems though.

    Problem 1:
    I've tried integrating the RANK with my current SQL and while it seems like it might work the RANK just keeps set to zero. I put "etc" for the image deliberately.
    PHP Code:
    SET @rownum 0, @rank 0, @prev_val NULL;
    SELECT
        site_games_list
    .box_art AS image,
        
    site_games_list.name AS game,
        
    COUNT(site_gift_transactions.game) AS top,
        @
    rownum := @rownum AS row,
        @
    prev_val := COUNT(site_gift_transactions.game) AS prev,
        @
    rank := IF (
            @
    prev_val != COUNT(site_gift_transactions.game),
            @
    rownum,
            @
    rank
        
    ) AS rank
    FROM site_gift_transactions
    INNER
        JOIN site_games_list
        ON site_games_list
    .id site_gift_transactions.game
    WHERE moderated 
    1
    GROUP BY game
    ORDER BY top DESC
    game 
    Code:
    ---------------------------------------------------
    | image | game          | top | row | prev | rank |
    ---------------------------------------------------
    |  etc  | Alan Wake     |  2  |  3  |  2   |  0   |
    |  etc  | Anno 2070     |  2  |  5  |  2   |  0   |
    |  etc  | AaAaAA!!!     |  1  |  4  |  1   |  0   |
    |  etc  | Dungeon Siege |  1  |  1  |  1   |  0   |
    |  etc  | The Sims 3    |  1  |  2  |  1   |  0   |
    ---------------------------------------------------
    Problem 2:
    This is trying another method from scratch with just the "game" column. It seems like everything is working but the rankings are all messed up plus the first rank is set to zero, I assume this is because I'm taking the rankings from the "game" rather than the COUNT, but if I took it from the COUNT it won't work and just give a ranking of zeros. Here's what I end up with (right is with "game", left is with COUNT):
    PHP Code:
    SET @rownum 0, @rank 0, @prev_val NULL;
    SELECT
        
    @rownum := @rownum AS row,
        @
    rank := IF (
            @
    prev_val!=game,
            @
    rownum,
            @
    rank
        
    ) AS rank,
        @
    prev_val := game AS game,
        
    count(game) as total
    FROM 

        
    SELECT game
        FROM site_gift_transactions
        WHERE moderated 
    1
        ORDER BY game
    site_gift_transactions
    group by game
    ORDER BY count
    (gamedescgame

    ====================================

    SET @rownum 0, @rank 0, @prev_val NULL;
    SELECT
        
    @rownum := @rownum AS row,
        @
    prev_val := count(game) AS prev,
        @
    rank := IF (
            @
    prev_val!=count(game),
            @
    rownum,
            @
    rank
        
    ) AS rank,
        
    game,
        
    count(game) as total
    FROM 

        
    SELECT game
        FROM site_gift_transactions
        WHERE moderated 
    1
        ORDER BY game
    site_gift_transactions
    group by game
    ORDER BY count
    (gamedescgame 
    Code:
    -------------------------------------	------------------------------
    | row | prev | rank | game  | total |	| row | rank | game  | total |
    -------------------------------------	------------------------------
    |  3  |  2   |  0   |  324  |   2   |	|  3  |  3   |  324  |   2   |
    |  4  |  2   |  0   |  335  |   2   |	|  4  |  4   |  335  |   2   |
    |  1  |  1   |  0   |  213  |   1   |	|  1  |  0   |  213  |   1   |
    |  2  |  1   |  0   |  226  |   1   |	|  2  |  2   |  226  |   1   |
    |  5  |  1   |  0   |  528  |   1   |	|  5  |  5   |  528  |   1   |
    -------------------------------------	------------------------------
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    dude...

    run this --
    Code:
    SELECT t.image
         , t.game
         , t.top 
      FROM ( SELECT site_games_list.id
                  , site_games_list.box_art AS image
                  , site_games_list.name AS game
                  , COUNT(site_gift_transactions.game) AS top 
               FROM site_games_list
             INNER 
               JOIN site_gift_transactions  
                 ON site_gift_transactions.game = site_games_list.id
                AND site_gift_transactions.moderated = 1 
             GROUP 
                 BY site_games_list.id ) AS t
     WHERE ( SELECT COUNT(*)
               FROM ( SELECT site_games_list.id
                           , COUNT(*) AS top
                        FROM site_games_list
                      INNER 
                        JOIN site_gift_transactions  
                          ON site_gift_transactions.game = site_games_list.id   
                         AND site_gift_transactions.moderated = 1            
                      GROUP
                          BY site_games_list.id ) AS x
              WHERE x.top > t.top ) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0
    Oh wow that worked perfectly.

    Sorry if I've been coming across a bit dim witted as I've never come across the RANK function before let alone do any nested queries. I have been trying all day and a fair bit last night so I definitely gave it a try.

    Looks like I need to get a dedicated MySQL book.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    thanks for the kind words

    this type of problem, in my opinion, is advanced sql

    so don't kill yourself if you can't do it right away

    it will come with practice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo