Forum

Posted by Sherman, Dec. 21, 2021, 2:07 p.m.

Why MAX() in select clause for question 39?

For question 39,
Write a query to return the name of the actor who appears in the most films.
You have to use INNER JOIN in your query.
Solution:
SELECT
FA.actor_id,
MAX(A.first_name) first_name,
MAX(A.last_name) last_name
FROM film_actor FA
INNER JOIN actor A
ON A.actor_id = FA.actor_id
GROUP BY FA.actor_id
ORDER BY COUNT(*) DESC
LIMIT 1;
Why is the MAX() function necessary for the select clause? I omitted both MAX() for A.first_name and A.last_name and it seemed to work fine to me

Answers

Hey Sherman,
Great question, the MAX function can be replaced by other aggregate function like MIN and it's a common trick to avoid syntax error since first_name and last_name were not included in the GROUP BY.
I am guessing your solution will GROUP BY all 3 columns (actor_id, first_name and last_name), which is another way to solve this problem and it is totally fine if you prefer that way.
Cheers,
Leon
SQLPad user avatar

Leon (949)

Dec. 21, 2021, 4:48 p.m.