MAX Function in Subquery-CodePudding

The situation:
I have a Borrower table with columns for ClientId and BorrowDate. I have a sub-query that finds the borrow dates grouped by ClientId for the year '2016' and the outer query finds the client who borrowed most.

The problem:
I am able to return the MAX of the COUNT grouped by ClientId of books borrowed, but when I query to also return the ClientId as well as the MAX function I get an error that says:

The multi-part identifier "Borrower.ClientId" could not be bound.

SELECT MAX(BorrowTimes) AS MostBorrowed
FROM (SELECT Borrower.ClientId, COUNT(Borrower.BorrowDate) AS BorrowTimes
      FROM Borrower
      WHERE Borrower.BorrowDate LIKE '2016%'
      GROUP BY Borrower.ClientId
    )AS SubBorrowed;

How can I change the query to also return the ClientId of the individual associated with the MAX function?

CodePudding user response：

You should be able to do without an outer top 1 nesting, just ensure you have your order by

SELECT TOP 1
      b.ClientId, 
      COUNT(*) AS BorrowTimes
   FROM 
      Borrower b
   WHERE 
      b.BorrowDate LIKE '2016%'
   GROUP BY 
      b.ClientId
   order by
      COUNT(*) desc

CodePudding user response：

Try below query. It will solve the issue.

SELECT TOP 1 
    COUNT(BorrowDate) MostBorrowed
FROM Borrower
WHERE BorrowDate LIKE '2016%'
GROUP BY ClientId
ORDER BY BorrowTimes DESC