sql - using MAX() and COUNT() with HAVING

Question

i have 2 tables movie:

insert into movie values ('mov1','2014-01-22','actor11');
insert into movie values ('mov2','2015-01-25','actor12');
insert into movie values ('mov1','2016-02-22','actor12');
insert into movie values ('mov1','2017-04-20','actor12');

dir:

insert into dir values ('d1','mov1','us',3);
insert into dir values ('d1','mov1','ind',3);
insert into dir values ('d2','mov2','uk',4);
insert into dir values ('d2','mov3','ind',3);

i want to find name of actor who has worked with most number of directors:

code:

SELECT actor, COUNT(actor) as c 
FROM   movie a  
       inner join dir b 
       on a.moviename=b.moviename  
GROUP BY actor
HAVING COUNT(actor)=(
                       SELECT MAX(mycount) 
                       FROM   ( 
                                  SELECT actor, COUNT(actor) as mycount 
                                  FROM   movie a 
                                         inner join dir b 
                                          on a.moviename=b.moviename 
                                  GROUP BY actor
                               )
                     );

i think the code is correct but im getting the error: Incorrect syntax near ')'.

Pls help!!

Answer 1

Try something like this

SELECT TOP (1) actor, COUNT(d.col1) as c 
FROM   movie a  
inner join dir b 
on a.moviename=b.moviename 
Group BY actor
ORDER BY c DESC