BecauseGeek BecauseGeek - 8 months ago 85
SQL Question

select donater with his max donation

I have Donaters table:

id,name,phone,address


And Donations table:

id,donater_id,donation_amount


what I want is to get all donaters with their max donation amount like this:

id,name,donation_amount


I have tried the following query to get the result.

select a.id,a.name,max(b.donation_amount) max_d
from donaters a left join donations b on b.donater_id = a.id
groub by a.id,a.name,max_d


However, it returns duplications.

Answer Source
select Dtrs.Id ,Dtrs.name,MAX_Donations.donation_amount from  
         Donaters Dtrs left join (
Select Dtrs.id Dtrs_id ,Max(Dons.donation_amount) donation_amount  from Donations Dons 
         inner join Donaters Dtrs on Dons.donater_id = Dtrs.id 
Group By Dtrs.id) Max_Donations on Max_Donation.Dtrs_id = Dtrs.id

Here for more information

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download