CODEWITHSUNDEEP
sqlsql-server
AIV
AIV

Reputation: 47

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

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

Upvotes: 2

Views: 95

Answers (2)

StuartLC
StuartLC

Reputation: 107247

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;

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 2

Related Questions