sql server count of max values without subquery
12,631
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.
Author by
user130582
Updated on June 04, 2022Comments
-
user130582 almost 2 years
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