MAX Function in Subquery

Question

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?

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Feb 21 '22 at 16:38
Not tested as yet, but will do. Been a long day, so may not have grapsed the request 100% :) `select top 1 * from ( SELECT Borrower.ClientId, COUNT(Borrower.BorrowDate) over (partition by borrower.clientid) AS BorrowTimes FROM Borrower WHERE Borrower.BorrowDate LIKE '2016%' ) as q order by q.BorrowTimes desc` — Nathan_Sav, Feb 21 '22 at 17:01
`WHERE Borrower.BorrowDate LIKE '2016%'` is not sargeable, you should use `WHERE Borrower.BorrowDate >= '20160101' AND Borrower.BorrowDate < '20170101'` — Charlieface, Feb 21 '22 at 17:37
Check this question. It will solve your issue. https://stackoverflow.com/questions/2436820/can-i-do-a-maxcount-in-sql#:~:text=To%20get%20one%20row%20with,John%20Travolta'%20GROUP%20BY%20c. — Md. Tarikul Islam Soikot, Feb 21 '22 at 17:38
Learn to ask smart questions. A question about querying data from a database should include a script that contains DDL and sample data in the form of insert statements. Guessing about how you defined "BorrowDate" shouldn't be required. Do you understand how `BorrowDate LIKE '2016%` is interpreted and the comment about it? These are important things to learn and understand to write effective, efficient SQL that is resistant to errors. Lastly, have you considered that a person might borrow multiple copies of the same book on the same date? It is unusual but also possible. — SMor, Feb 21 '22 at 18:43

score 0 · Answer 1 · answered Feb 21 '22 at 17:05

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

score 0 · Answer 2 · answered Feb 21 '22 at 17:49

0

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

answered Feb 21 '22 at 17:49

Md. Tarikul Islam Soikot

455
1
4
14

MAX Function in Subquery

2 Answers2