Question: I have this table:

```
actors(id: int, first_name: string, last_name: string, gender: string)
directors(id: int, first_name: string, last_name: string)
directors genres(director id: int, genre: string, prob: float)
movies(id: int, name: string, years: int, rank: float)
movies directors(director id: int, movie id: int)
movies genres(movie id: int, genre: string)
roles(actor id: int, movie id: int, role: string)

```

I want to find the list of actors who have acted in all the comedy movies directed by Wilson Yip in the year 2002.

I am doing the following:

```
select distinct A.first_name, 
       A.last_name, 
       M.name
  from actors A inner join roles R 
                      on A.id=R.actor_id
                inner join movies M 
                      on M.id=R.movie_id
  where M.name in ( select M.name
                      from movies M inner join movies_genres G 
                                       on M.id = G.movie_id
                                    inner join movies_directors MD 
                                       on M.id = MD.movie_id
                                    inner join directors D 
                                       on D.id = MD.director_id
                     where G.genre = 'Comedy' 
                       and M.year = 2002 
                       and D.first_name='Wilson' 
                       and D.last_name='Yip');

```

But, this gives me list of actors who have acted in a comedy movie directed by Wilson in 2002, but I want actors who have acted in 'all' such comedies.

How to do that?

Answer: 
TOP can be easily rewritten using ROW\_NUMBER:

```
select *
from tab
qualify
   row_number() over (partition by id order by date) = 1

```
You can do this using `row_number()`:

```
select t.*
from (select t.*,
             row_number() over (partition by id order by date) as seqnum
      from table t
     ) t
where seqnum = 1;

```