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.

Share:
12,631
user130582
Author by

user130582

Updated on June 04, 2022

Comments

  • user130582
    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