-1

My code is as seen below, but I'm not getting the desired answer, which is to get the full name of the actor whose done the maximum number of movies.

SELECT CONCAT(" ",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;

enter image description here

GMB
  • 216,147
  • 25
  • 84
  • 135
Pratham_Amitabh
  • 61
  • 2
  • 11
  • 1
    What is wrong with the results you are getting? – GMB Aug 01 '20 at 20:40
  • No sample testcases passed. (0/1) Testcase #1 Status FailedExecution time 3.60s CPU 0s Memory 6MB Description Testcase failed! The solution's output doesn't match the expected output. Evaluation logs Solution output *************************** 1. row *************************** PENELOPE GUINESS – Pratham_Amitabh Aug 01 '20 at 20:44

2 Answers2

1

Your query should do what you want, but you need to fix the limit clause. If youw want the one actor with most movies, then you need limit 1 rather than limit 2, 1. The concat() in the select clause also needs to be fixed - you want the space between the names, not before them.

Another option is to use an aggregate subquery for sorting, which avoids aggregation in the outer query:

select concat(a.first_name, ' ', a.last_name) as full_name
from actor a
order by (select count(*) from film_actor fa where fa.actor_id = a.actor_id) desc
limit 1

Note that neither your query nor this one take in account the possibility of top ties. Your question does not specify that. If that's what needed here, then the strategy would be different - but it seems this is not what you asked for.

GMB
  • 216,147
  • 25
  • 84
  • 135
1

If you dont want to use inner query and use only joins , this is the answer :

SELECT concat(FIRST_NAME," ",LAST_NAME) as full_name FROM ACTOR A INNER JOIN FILM_ACTOR FA on A.ACTOR_ID=FA.ACTOR_ID GROUP BY (FA.ACTOR_ID) ORDER BY count(FA.FILM_ID) DESC LIMIT 1
Syscall
  • 19,327
  • 10
  • 37
  • 52
Ankita Bhatt
  • 11
  • 1