select donater with his max donation

Question

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.

i tried this:: 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 but it returns duplications — BecauseGeek, Oct 03 '17 at 06:55

Radim Bača · Answer 1 · 2017-10-03T06:51:33.623

1

Simply use GROUP BY and MAX

  SELECT dr.id, dr.name, MAX(ds.donation_amount) as donation_amount
  FROM donaters dr
  JOIN donations ds On dr.id = ds.donater_id
  GROUP BY dr.id, dr.name

This solution will miss the donators who did not donated anything. If you need such donators use LEFT JOIN and ISNULL function.

edited Oct 03 '17 at 06:51

answered Oct 03 '17 at 06:47

Radim Bača

10,646
1
19
33

You're faster, I will delete my answer, yours is better :) – Víctor López Oct 03 '17 at 06:51

score 0 · Answer 2 · answered Oct 03 '17 at 06:49

0

This should work just fine, a Basic left outer join on the basis of the id and then aggregate max function on donation_amount.

SELECT a.id,a.name,max(donation_amount) from Donaters a
LEFT outer JOIN Donations b
ON a.id=b.id
group by a.id,a.name

answered Oct 03 '17 at 06:49

Jayesh Mulwani

655
6
19

The on clause is wrong, you should check donaters.id and donations.donation_amount – Víctor López Oct 03 '17 at 06:51
duplication if donater has donated twice this will return two records for him – BecauseGeek Oct 03 '17 at 06:57

Arsalan · Accepted Answer · 2017-10-03T07:39:10.470

0

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

edited Oct 03 '17 at 07:39

answered Oct 03 '17 at 07:09

Arsalan

709
2
14
27

this is working, but i had to change the first inner join to left join to get those who haven't donated yet – BecauseGeek Oct 03 '17 at 07:37
@BecauseGeek yes for getting those people who don't donate anything yet you should use left join – Arsalan Oct 03 '17 at 07:39

select donater with his max donation

3 Answers3