I'm trying to do a query of child objects that looks like this
List<Child__c> childList = new List<Child__c>(); childList = [SELECT Id, Status__c, Team_Member__c, Deactivation_Date__c FROM Child__c WHERE Status__c = 'Inactive'];
An issue arises in my code when this query returns 2 (or more) child records with the the same value in the Team_Member__c field. In that case, I need to get only the record with the most recent Deactivation_Date__c.
EX: If I had a set of records like this:
ID = 001; Team Member = Bob; Deactivation Date = Today(); Status=Inactive
ID = 002; Team Member = Sally; Deactivation Date = Yesterday(); Status=Inactive
ID = 003; Team Member = Jill; Deactivation Date = Today(); Status=Inactive
ID = 004; Team Member = Bob; Deactivation Date = Yesterday(); Status=Inactive
Then I would only want to return IDs 001, 002, and 003 because ID 001 Deactivation date > ID 004 Deactivation date
Has anyone had any experience with this? Is it possible to do a GROUP BY and MAX to return the desired results? Any help is greatly appreciated. Thank you!