Selecting highest value from duplicate records

Question

Post reply

Selecting highest value from duplicate records

BlackIceAngel

SSC Enthusiast

Points: 127
More actions
April 13, 2010 at 1:35 am

#96650

Good Day
This is my first post so please be gentle
In our debt table we have a Balance field that is always changing and there are multiple
values for specific MemberIDs.
This is my SQL statement
SELECT Member_Debt.Balance, Member.MemberID, Member.Member_Name FROM Member INNER JOIN Member_DEBT ON Member.MemberID = Member_Debt.MemberID WHERE Member_Debt.MemberID IN (3204, 3204, 2, 322, 1395, 1415, 1803, 2076, 2531, 2718, 11488, 10940, 2972, 3006, 3076, 3238, 3312, 3656, 3698, 3704, 5290, 8338, 8491, 8637, 8946, 8992, 9015, 9160, 9234, 9302, 9490, 9578, 9770, 10011, 10639, 10679, 10681, 10895, 11038, 11490, 11502, 11644, 11751, 13225, 10305, 16058, 8985, 9442, 9442, 9075, 16058, 16058, 14604, 9075, 16867, 16628, 9511, 16058, 16058, 8334, 14860, 3450, 13225, 3204) AND Active = 'Full' AND Member_Debt.Balance > 0 ORDER By Balance Desc
The problem is it returns all the Balances for a particular MemberID. I just want the highest Balance. The IDs I've put in here are dynamic, there could be 10 or 100 at any given time. How can I do this in one statement? My output needs to be the balance the id and the name of the member.
Any help would be greatly appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick:

SELECT Balance = ( SELECT TOP 1

Balance

FROM Member_Debt

WHERE Member_Debt.MemberID = Member.MemberID

AND Member_Debt.Balance > 0

ORDER By Balance Desc

),

Member.MemberID,

Member.Member_Name

FROM Member

WHERE Member.MemberID IN ( 3204, 3204, 2, 322, 1395, 1415, 1803, 2076, 2531,

2718, 11488, 10940, 2972, 3006, 3076, 3238, 3312,

3656, 3698, 3704, 5290, 8338, 8491, 8637, 8946,

8992, 9015, 9160, 9234, 9302, 9490, 9578, 9770,

10011, 10639, 10679, 10681, 10895, 11038, 11490,

11502, 11644, 11751, 13225, 10305, 16058, 8985,

9442, 9442, 9075, 16058, 16058, 14604, 9075,

16867, 16628, 9511, 16058, 16058, 8334, 14860,

3450, 13225, 3204 )

AND Active = 'Full'

I assumed Active is a column from Member and not from Member_Debt.

Hope this helps

Gianluca

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

I don't have SQL2000 at hand, so I can't try, but here's an alternative way to solve it that could be faster or slower. Give it a try.

SELECT Balance = ( SELECT MAX(Balance)