Convert UNION selects to single select with loop in MySQL

Question

I have this ugly code here in MySQL 8.0.

(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Adventure'
        group by genre_name, actor_id 
        ORDER BY count(actor_id) DESC
        limit 3)        
UNION 
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Music'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Fantasy'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Romance'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Family'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Comedy'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Short'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Animation'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Action'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Horror'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Sci-Fi'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Thriller'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Crime'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Drama'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Documentary'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Musical'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Mystery'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='War'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Western'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Adult'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3)
UNION
(SELECT genre_name, actor_id, count(actor_id)
       FROM movie_has_genre, role, movie, genre
        where role.movie_id=movie.movie_id
        and movie_has_genre.movie_id=movie.movie_id
        and genre.genre_id=movie_has_genre.genre_id
        and genre_name='Film-Noir'
        group by genre_name, actor_id
        ORDER BY count(actor_id) DESC
        limit 3);

and I want to convert it to be more concise. Basically, the only thing that changes is the genre_name condition. The 21 values for this condition can be found running: select genre_name from genre; How can I loop through the 21 values of genre_name and plug them into the select query, incrementally building up the UNION query? Thanks.

See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, May 26 '18 at 10:26
@SteliosM . . . *Never* use commas in the `FROM` clause. *Always* use proper, explicit, STANDARD `JOIN` syntax. Second, *always* qualify all your column references when a query references more than one table. How is anyone else supposed to know where the columns come from? — Gordon Linoff, May 26 '18 at 11:40
For few but huge groups a UNION (ALL) query is the most performant and quite simple to create dynamically in the application language. So you might already have he best solution. — Paul Spiegel, May 26 '18 at 19:51

Lukasz Szozda · Answer 1 · 2018-05-26T17:44:26.907

0

You could use ROW_NUMBER:

WITH cte AS (
  SELECT genre_name, actor_id, count(actor_id) AS cnt
    ,ROW_NUMBER() OVER(PARTITION BY genre_name ORDER BY count(actor_id) DESC) rn
  FROM movie
  JOIN movie_has_genre ON movie_has_genre.movie_id=movie.movie_id
  JOIN role            ON role.movie_id=movie.movie_id
  JOIN genre           ON genre.genre_id=movie_has_genre.genre_id  
  group by genre_name, actor_id 
)
SELECT *
FROM cte
WHERE rn <=3;

Please do not use obsolete comma join syntax.

How it works:

calculate count per genere_name and actor_id
get 3 most values per genre_name

edited May 26 '18 at 17:44

answered May 26 '18 at 10:22

Lukasz Szozda

162,964
23
234
275

MySql does not support ROW_NUMBER() OVER PARTITION BY – Pankaj Kumar May 26 '18 at 11:25
I think this is close, but the partition shouldn't include actor_id. Per genre, you just want the rows with the highest counts (which are already per actor in the first CTE) – Nik Shenoy May 26 '18 at 11:48
1

@PankajKumar Of course it does. [ROW_NUMBER() in MySQL](https://stackoverflow.com/questions/1895110/row-number-in-mysql/46753800#46753800) – Lukasz Szozda May 26 '18 at 11:49
@NikShenoy You maybe right. It is more understandable to get COUNT per genre not per (genre/actor). – Lukasz Szozda May 26 '18 at 11:51
No need for the 2nd cte, ROW_NUMBER is calculated *after* aggregation: `ROW_NUMBER() OVER(PARTITION BY genre_name ORDER BY COUNT(*) DESC) AS rn` – dnoeth May 26 '18 at 16:03

score 0 · Accepted Answer · answered May 26 '18 at 11:46

In MySQL (prior to 8.0), you can use variables for this:

select genre_name, actor_id, cnt
from (select genre_name, actor_id, cnt,
             (@rn := if(@g = genre_name, @rn + 1,
                        if(@g := genre_name, 1, 1)
                       )
             ) as rn
      from (select g.genre_name, r.actor_id, count(*) as cnt
            from movie m join
                 role r
                 on r.movie_id = m.movie_id join
                 movie_has_genre mhg
                 on mhg.movie_id = m.movie_id join
                 genre g
                 on g.genre_id = mhg.genre_id
            group by g.genre_name, r.actor_id
            order by g.genre_name, cnt desc
           ) ga cross join
           (select @g := '', @rn := 0) params
      ) ga
where rn <= 3;

In 8.0+, use row_number() the ANSI standard method for this functionality.

Notes:

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
Use table aliases that the abbreviations for the columns you are using.
Qualify all column names in the queries that you are using, especially when the FROM clause references more than one table.

Convert UNION selects to single select with loop in MySQL

2 Answers2

Linked