Show only the MAX values on count in SQL Server

Question

I have the below problem:

SELECT   
    B.ISBN, TITLE, COUNT(Rate) AS NumberOfRatings 
FROM 
    bda.books AS B
LEFT JOIN 
    bda.booksRatings as BR ON B.ISBN = BR.ISBN
GROUP BY 
    B.ISBN, Title

I wrote this query, and I want to get only the books with the highest ratings. The only way to do this I found is to add:

HAVING 
    COUNT(Rate) > 5

As the max values I get is 6. However I do not want to have this hardcoded.

Answer 1

Using TOP, all books with the highest rating.

SELECT top(1) WITH TIES
      B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 
FROM bda.books AS B
LEFT JOIN  bda.booksRatings as BR
      on B.ISBN = BR.ISBN
GROUP BY B.ISBN, Title
ORDER BY COUNT(Rate) DESC;

Answer 2

  SELECT TOP (1) WITH TIES 
    b.ISBN, 
    b.title, 
    RatingsCount = COUNT(*)
  FROM bda.booksRatings AS br
  INNER JOIN bda.books AS b
    ON br.ISBN = b.ISBN
  GROUP BY b.ISBN, b.title
  ORDER BY RatingsCount DESC;

Answer 3

You can either order by number of ratings and limit it to one like below:

SELECT   
    B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 

    FROM bda.books AS B
    LEFT JOIN 
    bda.booksRatings as BR
    on B.ISBN = BR.ISBN
    GROUP BY B.ISBN, Title Order by NumberOfRatings limit to 1

Or you can use the MAX function on NumberOfRatings like below:

select B.ISBN, TITLE,Max(NumberOfRatings) from (SELECT   
    B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 

    FROM bda.books AS B
    LEFT JOIN 
    bda.booksRatings as BR
    on B.ISBN = BR.ISBN
    GROUP BY B.ISBN, Title)

Both will work.