mysql, @variable and if statement

Question

guys, I follow instruction from this link

I created table like this

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

when i run this query

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 <= 2;

it doesn't give me 2 largest cities for each country

Is there something I missed? thanks

it doesn't work for me, i checked my mysql version and it says 5.0 maybe i should upgrade to 5.6 thanks @Barmar — iva arl
– iva arl, Commented Apr 3, 2015 at 3:51

Community · Accepted Answer · 2018-12-12 11:48:51Z

2

Add the below two line codes before your codes, and it will work.

SET @current_country:=NULL;
SET @country_rank:=0;


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 <= 2;

edited Dec 12, 2018 at 11:48

CommunityBot

11 silver badge

answered Apr 3, 2015 at 5:54

Tyshan Shi

363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2015-04-03 03:53:40Z

0

Try adding another subquery to initialize the variables:

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
CROSS JOIN (SELECT @country_rank := 0, $current_country := '') vars  
WHERE country_rank <= 2;

answered Apr 3, 2015 at 3:53

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

mysql, @variable and if statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related