Finding records with the most recent date (1 Viewer)

[KACJR]

Greetings to the well of knowledge...

I have a table of donations made by various individuals. Each individual may have made one or more donations over time. I need to find the one record for each individual that has the most recent donation date. I can't seem to find the right method for doing this. Can anyone point me in the right direction for this?

Regards,
Ken

 

[plog]

You would use an aggregate query, specifically using the MAX function in a sub query to find the most recent date for each donor:

https://www.techonthenet.com/sql/max.php


After you built that query, you would then use it to pull the record from your table with the date that matched that query for every donor.

 

[KACJR]

Oh, and further, if an individual made more than one donation on a given most recent date, I want to select the record that has the highest donation amount. I don't think I have any such records, but I've been asked to account for this.

 

[plog]

That is going to require another sub query.

sub1 - finds the most recent date of donation for each donor.

sub2 - finds the highest donoation on the most recent date for each donor.

main - pulls data from table that matches sub2

 

[arnelgp]

SELECT DonationTable.DonorName, Max(DonationTable.DonationDate) AS LatestDonationDate, (SELECT TOP 1 DonationAmount FROM DonationTable AS T1 WHERE T1.DonorName = DonationTable.DonorName AND T1.DonationDate=(SELECT MAX(DonationDate) FROM DonationTable AS T2 WHERE T2.DonorName = T1.DonorName) ORDER BY T1.DonationAmount DESC) AS BiggestDonation
FROM DonationTable
GROUP BY DonationTable.DonorName;


***
Change tablename and fieldname accordingly.

 

[Yattaro]

SELECT DonationTable.DonorName, Max(DonationTable.DonationDate) AS LatestDonationDate, (SELECT TOP 1 DonationAmount FROM DonationTable AS T1 WHERE T1.DonorName = DonationTable.DonorName AND T1.DonationDate=(SELECT MAX(DonationDate) FROM DonationTable AS T2 WHERE T2.DonorName = T1.DonorName) ORDER BY T1.DonationAmount DESC) AS BiggestDonation
FROM DonationTable
GROUP BY DonationTable.DonorName;


***
Change tablename and fieldname accordingly.

Hey arnelqp
I'm trying to follow along with your example as this is exactly what im trying to do:
In my table i have
CalHistory.Internal ID = DonationTable.DonorName
CalHistory.CalDate = DonationTable.DonationDate

Would i need another row in my table for DonationAmount?

Thanks for your time

 

[arnelgp]

you wouldn't be needint the DonationAmount unless same donor on same date, donated more than once.
otherwise you can just Link CallHistory and DonationTable (on those two fields you have) and show the DonationAmount.