klas mack klas mack - 2 years ago 118
SQL Question

Count number of ID in a table and select one or all top values(max) in POSTGRESQL

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
FROM actor
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

I havent been able to nest MAX() into this, but Im thinking this is what I need. All tips are welcome!

Answer Source

The best way to write this type of query is to use rank() or dense_rank():

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download