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.
sn country city 1 AAA aaa 2 BBB bbb 3 AAA ccc
country city country_rank AAA ccc 1 AAA aaa 2 BBB bbb 1
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