CODEWITHSUNDEEP
mysql
Aishwarya Wuntkal
Aishwarya Wuntkal

Reputation: 69

Write a query to find the full name of the actor who has acted in the third most number of movies in mysql?

I have used the code below for finding the actor with max movies but I would need the third actor now: 

SELECT CONCAT_WS(" ",actor.first_name,actor.last_name) AS Full_name 
FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY actor.actor_id
ORDER BY count(film_actor.actor_id) DESC
LIMIT 1;

Upvotes: 0

Views: 3384

Answers (1)

Jakob Em
Jakob Em

Reputation: 1090

You can pass a second value to LIMIT which indicates the offset for the query. Passing 2 as the offset and 1 as the limit would give you just the third row.

SELECT CONCAT_WS(" ",actor.first_name,actor.last_name) AS Full_name 
FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY actor.actor_id
ORDER BY count(film_actor.actor_id) DESC
LIMIT 2, 1;

Upvotes: 2

Related Questions