jeudi 2 avril 2015

mysql, @variable and if statement

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


Aucun commentaire:

Enregistrer un commentaire