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

So your top 2 cities should come in a single row(columns next to each other)? — nice_dev, Mar 25 '19 at 11:26
@vivek_23, no in seperate rows, I have added the expected results in the question — kingofbbq, Mar 25 '19 at 11:41
@Nick, I looked through all answers, I think none of them handle joining first and then selecting the top results — kingofbbq, Mar 25 '19 at 13:14
@kingofbbq sorry about that try https://stackoverflow.com/questions/13359640/mysql-how-to-get-x-number-of-results-per-grouping — Nick, Mar 25 '19 at 13:22
@kingofbbq Are you asking for the top 2 cities of each region, or are you asking for the top N cities? I've updated my answer and it is now working for the original question on 2 cities — kiks73, Mar 25 '19 at 14:00

score 2 · Accepted Answer · answered Mar 25 '19 at 13:37

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 |
  +------+-----------+---------+--------+---------+--------+

kiks73 · Answer 2 · 2019-03-25T13:56:41.697

0

You can try to use a UNION statement to get the FIRST and the SECOND best cities in the region. To get the second highest value I use this SO answer

SELECT  C.region, C.city, C.id, MAX(N.rating) as rating
FROM   cities C
INNER JOIN nightlife N
ON         C.ID = N.cityID 
GROUP BY C.region
UNION 
SELECT  C.region, C.city, C.id, MAX(N.rating) as rating
FROM   cities C
INNER JOIN nightlife N
ON         C.ID = N.cityID 
WHERE N.rating < (SELECT MAX(rating) 
                  FROM   nightlife 
                  INNER JOIN cities 
                  ON cities.ID = nightlife.cityID 
                  WHERE region = c.region)
GROUP BY C.region
ORDER BY region,rating desc,city

This is the working solution fiddle

edited Mar 25 '19 at 13:56

answered Mar 25 '19 at 11:51

kiks73

3,718
3
25
52

Thanks, this works too. @Strawberry 's answer is more dynamic though, because you can adjust the number of results per group. – kingofbbq Mar 25 '19 at 14:08
Yes, it is. I based my answer on the original question and on simplicity to obtain expected result – kiks73 Mar 25 '19 at 14:11

Devratna · Answer 3 · 2019-03-25T11:46:33.363

-1

What i understand your question is that you want desc order of rating data with city details:

    select b.city,b.country,a.region,a.rating from
  (SELECT max(c.id) as city_id,
       c.region,
       max(n.rating) as rating from cities c
  JOIN nightlife n
ON n.cityID = c.id
WHERE c.popular = true
group by c.region
ORDER BY n.rating
DESC) a inner join cities b on a.city_id=b.id;

edited Mar 25 '19 at 11:46

answered Mar 25 '19 at 11:27

Devratna

938
1
7
26

I want to get the the top 2 cities of each region. I have added the expected result in the question. – kingofbbq Mar 25 '19 at 11:40
see updated answer, i think you can get your expected result – Devratna Mar 25 '19 at 11:47

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

3 Answers3