This is frustrating me like crazy. I want to compare a count(*) to all other counts, and display the highest value that it finds.
Here is my current SQL:
select directinfo.directorID,directorinfo.firstname,directorinfo.lastname,count(directinfo.mvid) from genreinfo, directorinfo, directinfo where genreinfo.genre = 'Comedy' and directorinfo.directorID = directinfo.directorID and directinfo.mvID = genreinfo.mvID and directorinfo.directorID not in ( select directorID from directorinfo minus select directorid from directorinfo, genreinfo where directinfo.mvID =genreinfo.mvID and genreinfo.genre = 'Comedy' ) group by directinfo.directorID,directorinfo.firstname,directorinfo.lastname order by count(directinfo.mvid);
I want to compare count(directinfo.mvid) to find the highest count. I've attempted to code things like
HAVING count(directinfo.mvid) > ALL (SELECT count(directinfo.mvid) FROM directinfo))
But i'm not having any luck.
Any help would be appreciated! Thanks!