sql server count of max values without subquery

Question

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

 --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!)

I can do this correctly by INNER JOINing a subquery but it seems wasteful

 --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!)

So is there a way that is as simple as query #1, but returns the correct result as in query #2?

MS SQL Server 2000

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

Answer 1

I think that's right:

SELECT maxbooks.LibraryBranchId, maxbooks.maxDaysCheckedOut, count(*)
FROM books
INNER JOIN (
    SELECT LibraryBranchId, max(daysCheckedOut) AS maxDaysCheckedOut
    FROM books
    GROUP BY LibraryBranchId
) AS maxbooks
    ON books.LibraryBranchId = maxbooks.LibraryBranchId
    AND books.daysCheckedOut = maxbooks.maxDaysCheckedOut
GROUP BY maxbooks.LibraryBranchId, maxbooks.maxDaysCheckedOut

I don't think there's a simpler way - conceptually, it's the intersection of two sets. The set of tuples about the branches and the set of tupes satisfying that.

Answer 2

The easiest way to do this is to get your LibraryBranchId's and select your dayscheckedout then count them programmatically wherever you write the code and get the max dayscheckedout programmatically also.

Answer 3

How about this?

select LibraryBranchId, MAX(daysCheckedOut), count(daysCheckedOut)
from books B
where daysCheckedOut = (select MAX(daysCheckedOut) from books where LibraryBranchID = B.LibraryBranchID)
group by LibraryBranchId

Answer 4

Another "wasteful" way:

select LibraryBranchId, avg(daysCheckedOut) as maxDaysCheckedOut, count(*)
from
(
    select LibraryBranchId, daysCheckedOut
    from books b1
    where not exists 
    (
        select *
        from books b2
        where b2.LibraryBranchId = b1.LibraryBranchId
        and b2.daysCheckedOut > b1.daysCheckedOut
     )
) t
group by LibraryBranchId