I have a table(film_actor) that holds a relationship between two other tables, film and actor.
I want to count the number of occurences of actor.id in the table film_actor and then select the top value or values, but cant seem to get it to work.
My query so far is as below which gives me a nice list of all actors and the number of movies they have been in. What I cant seem to do is select the highest value or values. I know I can just limit the results to 1, or top 1, but I need it to be dynamic in case the list has 2 or more actors with the same number of movies.
SELECT actor.first_name, actor.last_name, COUNT(actor.actor_id) AS film_number
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY actor.actor_id, actor.first_name, actor.last_name
ORDER BY film_number DESC
The best way to write this type of query is to use
SELECT fa.* FROM (SELECT a.first_name, a.last_name, COUNT(a.actor_id) AS film_number, RANK() OVER (ORDER BY COUNT(a.actor_id) DESC) as seqnum FROM actor a INNER JOIN film_actor fa ON a.actor_id = fa.actor_id GROUP BY a.actor_id, a.first_name, a.last_name ) fa WHERE seqnum = 1;