I know I've done this before, but today, my head not think good.
I'm trying to write a simple query to pull the customer_no and cont_dt for the single largest campaign contribution (cont_amt) in a given fyear. I've gotten this far:
SELECT o.customer_no, MAX(o.cont_dt)FROM t_contribution oJOIN t_campaign g ON g.campaign_no = o.campaign_noWHERE g.fyear = 2010GROUP BY o.customer_no
Problem is, I don't get the cont_dt for each customer_no that I really want for my purposes. I know this is something simple. Any thoughts?
Thanks, Tessiturians.
BONUS BRAIN TRIVIA: 25% of a human's normal energy expenditure is spent on the brain.
Not sure why you’re not getting the customers you expect, but I think you’re also getting the highest contribution date within the given fiscal year, not the date of the highest contribution amount.
Checkout the help Brian and David gave me on almost exactly the same problem in this post: http://www.tessituranetwork.com/COMMUNITY/forums/p/3344/11122.aspx#11122
Kirk Mortensen
Database Administrator
TheatreWorks
Doh! I meant cont_amt instead of cont_dt, of course. Thanks for the other thread suggestion.