Subquery with WHERE clause => Column does not exist

I try to get the top 3 length per film for each actor. My query looks my this.

SELECT fullname, 
   ROW_NUMBER() OVER (PARTITION BY fullname ORDER BY length DESC) as f_rank,
   length
      FROM
   (SELECT actor.first_name || ' ' || actor.last_name as fullname, film.length
   FROM film_actor
   JOIN film
      ON film_actor.film_id = film.film_id
   JOIN actor
      ON film_actor.actor_id = actor.actor_id) sub
WHERE f_rank <= 3

So it looks like without the where clause

fullname    f_rank  length
Adam Grant  1   173
Adam Grant  2   173
Adam Grant  3   156
Adam Grant  4   155
Adam Grant  5   152
Adam Grant  6   148
Adam Grant  7   141

WHERE fullname = 'Alan Grant' works perfectly. With f_rank I get the following error:

column "f_rank" does not exist

What am I doing wrong here? I´m totally confused.

Answer

You can’t use a column alias defined in the select. But, it doesn’t make sense to do the ranking on the name of the actor when you can do it on the id. If that meets your needs, just move the logic to the subquery:

SELECT fullname, f_rank, length
FROM (SELECT a.first_name || ' ' || a.last_name as fullname, f.length,
             ROW_NUMBER() OVER (PARTITION BY a.actor_id ORDER BY f.length DESC) as f_rank
      FROM film_actor fa JOIn
           film f
           ON fa.film_id = f.film_id JOIN
           actor a
           ON fa.actor_id = a.actor_id
     ) af
WHERE f_rank <= 3