[Varsh]

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:

----------------
| 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?

[SimonJM]

Assign them a rank then add a clause 'allowing' only those of a given rank, or less.

[richpri]

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.

[Varsh]

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:

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,0 

The tables are as follows (in regards to the relevant columns used):

--------------------------
| 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)|
--------------------------

[r937]

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

[Varsh]

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.

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 + 1 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 

---------------------------------------------------
| 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):

SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT
    @rownum := @rownum + 1 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(game) desc, game

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

SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT
    @rownum := @rownum + 1 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(game) desc, game 

-------------------------------------	------------------------------
| 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   |
-------------------------------------	------------------------------

[r937]

dude...

run this --

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

[Varsh]

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.

[r937]

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