I have a table where I store the movie id (id_pel) and the actor id (id_ac), to relate movies with actors
I want to get the movie(s) with the most actors
With this query:
SELECT *, COUNT(id_AC) AS 'actores' FROM pelisactores GROUP BY id_pel
I get how many actors are involved in each movie and if I add the following to that query at the end:
ORDER BY actores DESC LIMIT 1
I get the movie with the most actors, but it only shows me one record. If I have, for example, 3 movies where 7 actors work, I want it to show me all 3, if they really are the movies where the most actors work, but with ORDER BY actors DESC LIMIT 1 it would only show me one record
With this other query:
SELECT *, COUNT(id_AC) AS 'actores' FROM pelisactores GROUP BY id_pel HAVING COUNT(actores) >= 7
I get all those movies where more than 7 actors work, but how do I make the query to get the movie or movies where more actors work?
This query gives me an error:
SELECT *, COUNT(id_AC) AS 'actores' FROM pelisactores GROUP BY id_pel HAVING MAX(COUNT(actores))
I'm not placing the MAX and COUNT correctly... how would it be done?
Thank you!
First of all, let's see what is the maximum of actors of all the movies
Now, knowing this, we simply have to list the movies that have this maximum number of actors:
An alternative is to use the
RANK()
or functionsDENSE_RANK()
to define the order.If you're using MySQL 8 or higher, you can use CTE's and window functions to accomplish this.
I explain in an example below:
Resumen
, I do the typical summary to obtain the number of actors per filmResumen
and the functiondense_rank()
to number the rows based on the number of actors.dense_rank
it has the advantage that it gives the same numbering for all the rows that have the same value in the fields by which I order, in this case, the number of actors.id_pel
but I'm doing a join with a hypothetical table of movies to get their names.P.S. Although there are already other answers that solve the problem correctly, I have added this one since they did not make use of CTE's and, in my opinion, using them is not only elegant, but also makes the code much easier to understand and maintain.