Maximum sql server size without subquery

Question

I want to write a T-SQL query that returns not only the maximum value, but also the number of rows that have the maximum value. There must be a better way than what I came 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 it correctly INNER JOINing in 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 skipped two important GROUP BY above, on my first attempt when I entered it, I added them EDIT: Pretend that the version written by Cade Roux is what I wrote

Answer 1

I think that is correct:

 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 do not think that there is an easier way - conceptually, this is the intersection of two sets. A lot of tuples around branches and a lot of dulls satisfying this.

Answer 2

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

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