CODEWITHSUNDEEP
mysql
Leslie
Leslie

Reputation: 107

MYSQL I need to remove older records from my result set

I have almost got it, but not quite. I am returning the data I need but I only want to output the latest paymentDate for each memberID.

My query is as follows:

SELECT a.mID, a.ageGroup,a.dayAvail,a.startTime,b.memberType,b.paymentDate,b.id FROM referrals a
GROUP BY b.id
LEFT JOIN membership b ON a.mID=b.memberID
WHERE a.ageGroup='Young Adult'
ORDER BY b.memberID,b.paymentDate DESC

My result returned is:

memberID    ageGroup      dayAvail  startTime   memberType    paymentDate    b.id
226      Young Adult    Wednesday    6:30 PM    Associate      8/31/2013     869
226      Young Adult    Wednesday    6:30 PM    Associate1     14/30/1993    158
1262    Young Adult    Thursday    7:00 PM    Clinical        7/1/2013      762
1262    Young Adult    Thursday    7:00 PM    Clinical        3/23/2010     610
1271    Young Adult    Tuesday    5:30 PM    Clinical         8/1/2013      687
1271    Young Adult    Tuesday    5:30 PM    Associate1      16/18/2010     619

Based upon paymentDATE for each memberID, I only want the LATEST paymentDate returned (or I guess, the LAST b.id for each memberID)

Thank you.

Upvotes: 0

Views: 37

Answers (1)

sarwar026
sarwar026

Reputation: 3821

You should GROUP BY with a.mID instead of b.id. Please try with the following query

SELECT a.mID, a.ageGroup,a.dayAvail,a.startTime,b.memberType,MAX(b.paymentDate),b.id 
FROM referrals a    
LEFT JOIN membership b ON a.mID=b.memberID
GROUP BY a.mID
WHERE a.ageGroup='Young Adult'
ORDER BY a.mID

Upvotes: 1

Related Questions