CODEWITHSUNDEEP
sqlsql-server
Emz
Emz

Reputation: 21

Need SQL MAX please

I have a table with a list of Client No's, ID etc & there are many Clients with different ID's. I need to pull out the MAX ID No. for each Client, e.g.

ClientNo: 1500 has 3 ID's - the maximum in the ID field is the one I need!

UPDATE: This works:

SELECT MP.ClientID, MP.SequenceID
FROM  TABLENAME MP
INNER JOIN (
SELECT ClientID, MAX(SequenceID) SequenceID
FROM  TABLENAME
GROUP BY ClientID
) b on MP.ClientID = b.ClientID AND MP.SequenceID = b.SequenceID

BUT....

I need to link the table to many others to pull in other data, where do I insert the left joins to these tables please?

Upvotes: 2

Views: 41

Answers (2)

Shekar Kola
Shekar Kola

Reputation: 1297

You may want to do this:

SELECT MP.ClientID, b.DesiredValue
FROM  TABLENAME MP
INNER JOIN (
            SELECT ClientID, DesiredValue, ROW_NUMBER () OVER (PARTITION BY ClientID order by SequenceID desc) As RecentRN
            FROM  TABLENAME
            ) b 
        on MP.ClientID = b.ClientID AND b.RecentRN = 1

or

SELECT MP.ClientID, b.DesiredValue
FROM  TABLENAME MP
INNER JOIN (
            SELECT ClientID, DesiredValue,ROW_NUMBER () OVER (PARTITION BY ClientID order by SequenceID desc) As RecentRN
            FROM  TABLENAME
            ) b 
        on MP.ClientID = b.ClientID
Where b.RecentRN = 1

Upvotes: 0

fiveobjects
fiveobjects

Reputation: 4337

I am assuming you have multiple same client (numbers) with different IDs and for each client (number) you have to get the maximum ID. You may do the following:

select client_number, max(ID) from client group by client_number;

Depending on your need tweak this query.

Upvotes: 2

Related Questions