All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0