title: sql server count of max values without subquery
body: <p>I want to write a T-SQL query which returns not only the maximum value, but the number of rows having the maximum value.  There must be a better way than what I have come up with</p>
  
  <pre><code> --wrong way 
   select LibraryBranchId, max(daysCheckedOut), count(daysCheckedOut) 
   from books group by LibraryBranchId
  
   LibraryBranchId   Expr1      Expr2
   ----------------------------------
   1                 100       398503  (WRONG!)
   2                 75         94303  (WRONG!)
   3                 120       103950  (WRONG!)
  </code></pre>
  
  <p>I can do this correctly by INNER JOINing a subquery but it seems wasteful</p>
  
  <pre><code> --right way, but seems WAY too long
   select LibraryBranchId,max(daysCheckedOut),count(daysCheckedOut)
   from books inner join 
     ( select LibraryBranchId, max(daysCheckedOut) as maxDaysCheckedOut
       from books group by LibraryBranchId ) as maxbooks 
   on books.LibraryBranchId=maxbooks.LibraryBranchId
   where daysCheckedOut=maxDaysCheckedOut
   group by LibraryBranchId 
  
   LibraryBranchId   Expr1      Expr2
   ----------------------------------
   1                 100           17  (RIGHT!)
   2                 75            11  (RIGHT!)
   3                 120            2  (RIGHT!)
  </code></pre>
  
  <p>So is there a way that is as simple as query #1, but returns the correct result as in query #2?</p>
  
  <p>MS SQL Server 2000</p>
  
  <p>EDIT: I missed two important GROUP BYs above on my first try at entering this, I have added them
  EDIT: Pretend that the version that Cade Roux wrote is what I wrote</p>