How to get top n solutions by group/category after join in mysql?

Question

I am joining 2 tables and then want the top n results for each group ordeed by a column from the second table. My mysql version does not allow me to use row_number() and WITH clause.

With this query I can join my cities and nightlife tables:

SELECT cities.id, cities.city, cities.country, cities.region, nightlife.rating
JOIN nightlife ON nightlife.cityID = cities.id
WHERE cities.popular = true 
ORDER BY nightlife.rating DESC;

With this query I can get 2 cities for each region ordered by the highest id:

SELECT id, city, country, region
FROM cities
WHERE cities.popular = true
AND
(
   SELECT count(*) FROM cities AS c
   WHERE c.region = cities.region AND c.id >= cities.id
) <= 2;

How do I combine them together in one query get the top 2 cities for each region with the highest nightlife rating... without using row_number() and WITH clause?

Expected Result

 id | city      | country | region | nightlife_rating
 ----------------------------------------------------
 1  | barcelona | spain   | europe | 10.0
 5  | paris     | france  | europe | 9.0
 23 | shanghai  | china   | asia   | 9.5
 54 | tokyo     | japan   | asia   | 9.3
 ...

Sample schema..

CREATE TABLE cities (
    id int(11),
    city varchar(255),
    country varchar(255),
    region varchar(255),
    popular bool
);
INSERT INTO cities (id, city, country, region, popular)
VALUES  (1, 'barcelona', 'spain', 'europe', true),
        (3, 'rome', 'italy', 'europe', true),
        (5, 'paris', 'france', 'europe', true),
        (23, 'shanghai', 'spain', 'asia', true),
        (33, 'seoul', 'south-korea', 'asia', true),
        (54, 'tokyo', 'japan', 'asia', true);

CREATE TABLE nightlife (
    cityID int,
    rating float
);
INSERT INTO nightlife (cityID, rating)
VALUES  (1, 10.0),
        (3, 8.3),
        (5, 9.0),
        (23, 9.5),
        (33, 8.7),
        (54, 9.3);

And SQL Fiddle of same...

http://sqlfiddle.com/#!9/ccc4e4

Answer 1

SELECT n.id
     , n.city
     , n.country
     , n.region
     , n.popular
     , n.rating
  FROM 
     ( SELECT a.*
            , CASE WHEN @prev = region THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=region prev 
         FROM
            ( SELECT c.*
                   , n.rating
                FROM cities c 
                JOIN nightlife n 
                  ON n.cityid = c.id 
               ORDER 
                  BY region
                   , rating DESC
            ) a
         JOIN ( SELECT @prev:=null,@i:=0) vars
      ) n
  WHERE n.i <= 2;

  +------+-----------+---------+--------+---------+--------+
  | id   | city      | country | region | popular | rating |
  +------+-----------+---------+--------+---------+--------+
  |   23 | shanghai  | spain   | asia   |       1 |    9.5 |
  |   54 | tokyo     | japan   | asia   |       1 |    9.3 |
  |    1 | barcelona | spain   | europe |       1 |     10 |
  |    5 | paris     | france  | europe |       1 |      9 |
  +------+-----------+---------+--------+---------+--------+