Show only the MAX values on count in SQL Server-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

1.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 user 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

CodePudding user response：

  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;