Inner query - last date clause

Question

I have a trigger on after Insert and after update on Contact. This trigger updates Account.SalatySumLastContactsLast, with sum of Contact.Salary where Contact.mydate__c is latest. I simplified the logic for the question, I have to use trigger.

How can I wrote the query to select only contacts with contact.mydate__c is the latest.

SELECT id,(SELECT id FROM Contacts WHERE myDate ... ) from Account 

Answer 1

You can order by mydate__c to get the contact where mydate__c is latest.

SELECT Id,(SELECT Id FROM Contacts ORDER BY mydate__c DESC LIMIT 1) FROM Account 

updated Code:

map<String,Date> latestDateMap=new map<String,Date>();
for(Account acc : [SELECT Id,RollupSummary__c FROM Account /*WHERE Your filter*/]){
 latestDateMap.put(acc.Id,acc.RollupSummary__c );
}

List<Contact> conList=[SELECT Id FROM Contact WHERE AccountId IN :latestDateMap.keySet() AND mydate__c IN :latestDateMap.values()];