MYSQL - query to find top 10 favourite locations in each city

Here's a short example of using MYSQL CASE WHEN Control flow function alongwith User defined variables


SELECT CITY,Location,Most_Popular
FROM
(
SELECT @CITY,
CASE
WHEN (@CITY=CITYNAME FIELD AND @ROWNUM < 10) THEN (@ROWNUM:=@ROWNUM+1)
WHEN (@CITY != CITYNAME FIELD) THEN @ROWNUM:=1
ELSE @ROWNUM:=10
END AS CHANGED_CITY, @CITY:=CITYNAME FIELD, D.*
FROM (SELECT @ROWNUM:=0) R, (SELECT @CITY:='0') M,
(
SELECT CITY, Location, count(UserId Field) as Most_Popular
FROM Table Names
WHERE
Field Constraints
group by CITY,Location
order by CITY,Most_Popular Desc
)D
)D1
GROUP BY CITY,CHANGED_CITY
order by CITY,Most_Popular Desc;

Thanks to Ankit for helping me out on this...