Write a SQL query that ranks the top-10 actors from the US by number of films th

Question

Write a SQL query that ranks the top-10 actors from the US by number of films they played in. The result should show each actor by first name and last name, and then show the number of films the actor has played in:

The top-10 list should be a standard (competition) ranking ordered be ranks with regard to the number of films an actor played in; for actors who played in the same number of films, they should have the same rank, and within that rank being shown in alphabetical order of their name (first and last name).

Note that we are asking for the Top-10 here with regard to such a competitive ranking, which should include all actors whose rank is between 1 and 10.

rank first_name last_name films 1 SUSAN DAVIS 54 2 WALTER TORN 41 ... ... ... ...

Explanation / Answer

select first_name , last_name, films from ( select first_name , last_name, films,row_number() over(order by films desc) rn from ( select first_name , last_name, count(A.film_id) films from Film A, Film_Actor B , Actor C where A.film_id=B.film_id and B.actor_id=C.actor_id group by film_id ) ) where rn

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---