title: mysql, @variable and if statement
body: <p>guys,
  I follow instruction from this <a href="http://www.sqlines.com/mysql/how-to/get_top_n_each_group" rel="nofollow">link</a></p>
  
  <p>I created table like this</p>
  
  <pre><code>CREATE TABLE cities
  (
    city VARCHAR(80),
    country VARCHAR(80),
    population INT
  );
  
  INSERT INTO cities VALUES ('New York', 'United States', 8175133); 
  INSERT INTO cities VALUES ('Los Angeles', 'United States', 3792621); 
  INSERT INTO cities VALUES ('Chicago', 'United States', 2695598); 
  
  INSERT INTO cities VALUES ('Paris', 'France', 2181000);
  INSERT INTO cities VALUES ('Marseille', 'France', 808000);
  INSERT INTO cities VALUES ('Lyon', 'France', 422000);
  
  INSERT INTO cities VALUES ('London', 'United Kingdom',  7825300);
  INSERT INTO cities VALUES ('Birmingham', 'United Kingdom', 1016800);
  INSERT INTO cities VALUES ('Leeds', 'United Kingdom', 770800);     
  </code></pre>
  
  <p>when i run this query</p>
  
  <pre><code>SELECT city, country, population
    FROM
    (SELECT city, country, population, 
                @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
                @current_country := country 
      FROM cities
      ORDER BY country, population DESC
     ) ranked
     WHERE country_rank &lt;= 2;
  </code></pre>
  
  <p>it doesn't give me 2 largest cities for each country</p>
  
  <p>Is there something I missed? thanks</p>