Select the row with the max value in a specific column, SQL Server

Question

I've been working on a school project past few days and I picked to work on a DVD club database. I have six tables, but for this question, only two are relevant. The clients table and the loans table. So, what I am trying to do is count for every client how many loans he's made so far and out of all pick the client with the max number of loans, so he can be rewarded the free DVD next month. Here is the code I've written, but it doesn't pick the specific client, it shows all the clients having the max number of loans of a specific client:

SELECT tblClients.Client_ID, MAX(x.Number_Of_Loans) AS MAX_NOL
FROM 
(
SELECT COUNT(tblLoans.Client_ID) AS Number_Of_Loans 
FROM tblClients, tblLoans WHERE tblClients.Client_ID=tblLoans.Client_ID 
GROUP BY tblLoans.Client_ID
)x, tblClients, tblLoans
WHERE tblClients.Client_ID=tblLoans.Client_ID
GROUP BY tblClients.Client_ID, tblClients.Given_Name, 
tblClients.Family_Name, tblClients.Phone, tblClients.Address, tblClients.Town_ID

Madhivanan · Accepted Answer · 2014-04-04 09:10:07Z

2

Use the following

SELECT TOP 1 tblClients.Client_ID,COUNT(tblLoans.Client_ID) AS MAX_NOL 
FROM tblClients, tblLoans 
WHERE tblClients.Client_ID=tblLoans.Client_ID 
GROUP BY tblClients.Client_ID
ORDER BY COUNT(tblLoans.Client_ID) DESC

answered Apr 4, 2014 at 9:10

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AIV Over a year ago

Wow, thanks a lot, I've just changed GROUP BY to tblClients.Client_ID as I was getting an error.

StuartLC · Accepted Answer · 2014-04-04 09:20:55Z

You can do this with a single aggregate GROUP, ordered by the client with the max loans:

SELECT TOP 1 tblClients.Client_ID, tblClients.Given_Name, tblClients.Family_Name, 
       tblClients.Phone, tblClients.Address, tblClients.Town_ID, 
       COUNT(x.Number_Of_Loans) AS MAX_NOL
FROM
   tblClients INNER JOIN tblLoans 
     ON tblClients.Client_ID=tblLoans.Client_ID
GROUP BY tblClients.Client_ID, tblClients.Given_Name, tblClients.Family_Name,
         tblClients.Phone, tblClients.Address, tblClients.Town_ID
ORDER BY MAX_NOL DESC;

Any selected columns from the client need to be included in the GROUP, and I would recommend using JOINs instead of WHERE joins.

Edit
What might be tidier is to split the determination of the ClientId with the most loans and the concern of fetching the rest of the client's data, like so (rather than the ungainly GROUP BY over many columns):

SELECT c.Client_ID, c.Given_Name, c.Family_Name, 
       c.Phone, c.Address, c.Town_ID, 
       x.MaxLoans
FROM
   tblClients c
   INNER JOIN
     (SELECT TOP 1 tblClients.Client_ID, COUNT(tblLoans.Client_ID) AS MaxLoans
      FROM tblClients 
        INNER JOIN tblLoans 
        ON tblClients.Client_ID=tblLoans.Client_ID
      GROUP BY tblClients.Client_ID
      ORDER BY MaxLoans DESC) x
     ON c.Client_ID = x.Client_ID;

Collectives™ on Stack Overflow

Select the row with the max value in a specific column, SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related