Couldn't Get Result From Group By- Having Query

I'm trying to find an year when maximum number of movies were published having genre 'Mystery' with total count of movies. Correct answer is 2001 and 2 for this database. Below is

Solution 1:

Would something like this do the job?

SELECT TOP(1) movie.mov_year, COUNT(movie.mov_year) AS'total_movies'FROM 
    movie$ movie 
    JOIN movie_genres$ m_geners ON m_geners.mov_id = movie.mov_id
    JOIN genres$ geners ON geners.gen_id = m_geners.gen_id
WHERE geners.gen_title LIKE'%mystery%'GROUPBY movie.mov_year
ORDERBY COUNT(movie.mov_year) desc

Solution 2:

I was able to retrieve the result with TOP WITH TIES as suggested by Dale K. Below is the query:

SELECT TOP(1) WITH TIES mov_count,mov_year,rev_stars AS avg_rev_stars
FROM
( 
    SELECTCOUNT(m.mov_year) mov_count,    m.mov_year
            ,AVG(ISNULL(r.rev_stars,0)) rev_stars
    FROM movie$ m
    INNERJOIN movie_genres$ mg on m.mov_id=mg.mov_id
    INNERJOIN genres$ g on mg.gen_id=g.gen_id
    LEFTJOIN dbo.rating$ r on m.mov_id = r.mov_id
    GROUPBY m.mov_year, gen_title
    HAVING gen_title='Mystery'
) AS t1
ORDERBY t1.mov_count desc

Result: