I have three tables: Country, City, Restaurant. I would like to have a query that first calculates how many restaurants are for each city. I would only want to return those three cities for each country which has the most number of restaurants.
Only active ones should be counted (isactive=1)
I have tried everything I can think of, but I do not get correct list. Please, someone help!
the basic goes likes this:
select co.countryname, ci.cityname, count(r.restaurantid)
from restaurant r
join country co on r.countryid=co.countryid
join city ci on r.cityid=ci.cityid
group by co.countryname, ci.cityname
order by co.countryname, count(r.restaurantid) desc, ci.cityname
I have used that as a base to this and then tried everything in my power to have only the top 3 cities returned, but no success!