0

Why is the country_rank value 1 for each row, which I assume is due to STRCMP returing false everytime. I think @current_country is not set correctly but dont know why.

Table:

sn country city
1  AAA     aaa
2  BBB     bbb
3  AAA     ccc

Output Expected:

country city country_rank
AAA     ccc  1
AAA     aaa  2
BBB     bbb  1

Query:

SELECT country,city,
@country_rank := IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM table  
ORDER BY country,sn  DESC

The query is supposed to rank the cities in each country as per sn. MYSQL 5.6

1
SELECT country,city,
       @country_rank :=
          IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1)
          AS country_rank,
       @current_country := country  setctry
  FROM  cities,
        (select @current_country="", @country_rank=0) init
  ORDER BY country,sn  DESC

cfr. SQL Fiddle http://sqlfiddle.com/#!9/c388ef7/2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.