I'm trying to put together a custom view for some output elements that would give me various fields from the largest contribution for each customer.
So I've gotten this far in my select statement:
select customer_no, max(cont_amt) as cont_amtfrom vs_contributiongroup by customer_no
But how do I get the ref_no of this largest contribution in there so I can join to the full contribution record and get other info, like gift date, campaign, source, etc.?
Any help would be much appreciated!
Thanks,
Kirk Mortensen
TheatreWorks
Thanks Brian!
Your suggestion got me thinking a little more, and I adapted your thinking into this:
select a.customer_no, a.cont_amt, a.cont_dt, a.campaign_no –-etc.
from vs_contribution a
join (select aa.customer_no, max(aa.ref_no) as ref_no
from vs_contribution aa
join (select customer_no, max(cont_amt) as max_cont_amt
from vs_contribution
group by customer_no) bb
on aa.customer_no=bb.customer_no and aa.cont_amt=bb.max_cont_amt
group by aa.customer_no) b
on b.customer_no=a.customer_no and b.ref_no=a.ref_no
It does choose between different contributions with the same amount by selecting the largest ref_no (which should be the most recent gift of that amount), but I think it will do what I need. See any potential problems?
Database Administrator