MySQL Ranking of Subselect - Top N Results by Group

Question

I need to get a listing of top 2 Names in every Country (from Accounts & Country Table). I have searched a lot and also have found some valid answers, but cannot get the correct results.

Please see my SQL Fiddle here:

http://sqlfiddle.com/#!9/cd1296/5

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(6) unsigned NOT NULL,
  `iso` varchar(3) NOT NULL,
  `country_name` varchar(24) NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

INSERT INTO `country` (`id`, `iso`,`country_name`) VALUES
  ('1', 'DEU','Germany'),
  ('2', 'USA','United States'),
  ('3', 'CAN','Canada'),
  ('4', 'JPN','Japan');

CREATE TABLE IF NOT EXISTS `accounts` (
  id int(6) unsigned NOT NULL,
  name varchar(50) NOT NULL,
  iso3 varchar(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `accounts` (`id`,`name`, `iso3`) VALUES
  ('1', 'Hans', 'DEU'),
  ('2', 'Willi', 'DEU'),
  ('3', 'Peter', 'DEU'),
  ('4', 'Susanne', 'DEU'),
  ('5', 'John', 'USA'),
  ('6', 'Jane', 'USA'),
  ('7', 'Peter', 'USA'),
  ('8', 'Paul', 'USA'),
  ('9', 'Mary', 'USA'),
  ('10', 'Gerard', 'CAN'),
  ('11', 'Mirelle', 'CAN'),
  ('12', 'Hiko', 'JPN'),
  ('13', 'Miko', 'JPN'),
  ('14', 'Susanne', 'DEU'),
  ('15', 'Peter', 'DEU'),
  ('16', 'John', 'USA'),
  ('17', 'Paul', 'USA'),
  ('18', 'Susanne', 'DEU'),
  ('19', 'Bob', 'DEU'),
  ('20', 'John', 'USA'),
  ('21', 'Paul', 'USA'),
  ('33', 'Gerard', 'CAN'),
  ('22', 'Maribelle', 'CAN'),  
  ('23', 'Gerd', 'CAN'),
  ('24', 'Mira', 'CAN'),
  ('25', 'Huko', 'JPN'),
  ('26', 'Hako', 'JPN'),
  ('27', 'Hiko', 'JPN'),
('28', 'Jon', 'USA'),
('29', 'Jim', 'USA'),
('30', 'John', 'USA'),
('31', 'JJ', 'USA'),
('32', 'Bob', 'USA'),
('34', 'Bob', 'USA'),
('35', 'Miko', 'JPN'),
('36', 'Miko', 'JPN');

Using this Statement brings the list in correct order, but does not stop after the second result:

SELECT country_name, iso, name, COUNT(name) AS name_count
 FROM accounts
 JOIN country ON country.iso = accounts.iso3
 GROUP BY country.iso,  name
 ORDER BY country.iso ASC, name_count DESC;

As suggested in other questions/answers the solution could be use "MySQL session variables" (based on https://www.databasejournal.com/features/mysql/selecting-the-top-n-results-by-group-in-mysql.html).

My Problem: The country_rank is not populating correctly and so not giving corrent results. What I am doing wrong?

SET @current_country = ""; 
SET @country_rank = 0; 

 SELECT country_name, name, name_count, rank
 FROM
 (
    SELECT country_name, iso, name, COUNT(name) AS name_count,
    @country_rank := IF( @current_country = iso, 
                         @country_rank + 1, 
                         1 
                       ) AS rank, 
    @current_country := iso 
    FROM accounts
    JOIN country ON country.iso = accounts.iso3
    GROUP BY country.iso,  name
    ORDER BY country.iso ASC, name_count DESC
) AS ranked
WHERE rank<=2;

user_1491895 · Answer

You need to do the grouping in a subquery, so that the ranking will be done on the grouped results.

SELECT country_name, name, name_count, rank
FROM (
    SELECT country_name, iso, name, name_count,
        @country_rank := IF( @current_country = iso, 
                             @country_rank + 1, 
                             1 
                           ) AS rank, 
        @current_country := iso 
    FROM (
        SELECT country_name, iso, name, COUNT(name) AS name_count
        FROM accounts
        JOIN country ON country.iso = accounts.iso3
        GROUP BY country.iso,  name
        ORDER BY country.iso ASC, name_count DESC
    ) AS ordered
) AS ranked
CROSS JOIN (SELECT @country_rank = 0, @current_country = '') AS vars
WHERE rank<=2;

Fiddle

user_1144035 · Answer

MySQL does not guarantee the order of evaluation of expressions in a SELECT. Hence, it is dangerous to define a variable in one expression and then use it in another. That is, assigning the variables and using them should all be in one expression.

The issues can be intermittent, so code can look like it works in one context, but not work in another. So I would recommend writing this as:

SELECT country_name, name, name_count, rank
FROM (SELECT country_name, iso, name, name_count,
             (@rn := IF(@c = iso, @rn + 1,
                        IF(@c := iso, 1, 1)
                       )
             ) as rank
      FROM (SELECT c.country_name, c.iso, a.name, COUNT(*) AS name_count
            FROM accounts a JOIN
                 country c
                 ON c.iso = a.iso3
            GROUP BY country.iso,  name
            ORDER BY c.country_name, c.iso ASC, name_count DESC
           ) c CROSS JOIN
           (SELECT @c := '', @rn := 0) params
      ) c
WHERE rank <= 2;

MySQL Ranking of Subselect - Top N Results by Group

2 Answers