function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Phuc Nguyen 18Phuc Nguyen 18 

Get max date from Child records based on order

Hello All,
Have an interesting issue.  I need to get the max date from a list of child records but the result needs to be based on an order.

Example
I have Test record 1
It has child records

E 07/02/2021
C 05/22/2022
B 03/05/2021
D 06/02/2020
B 03/05/2019
F 01/01/2021

Now the max date of teh child records is C 05/22/2022.
But I need to get the max date based on an order.
The Order is A,B,C,D,E,F
And since there is a B record I need to get B 03/05/2021 since this is the max date for the B records.

If there was an 'A' record I would have neede to get the 'A' record even if the date value was not the max within all of of the child records.

Any suggestion is appreciated.
Cheers,
P
Best Answer chosen by Phuc Nguyen 18
mukesh guptamukesh gupta
Hi Phuc,

Please follow below code:-
  
List<AggregateResult> contactAggrList = [SELECT AccountId, max(dateField__c) 
                                                 FROM Contact 
                                                 Where AccountId =: accList                                                
                                                 ORDER BY dateField__c DESC];



if you need any assistanse, Please let me know!!

Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh

All Answers

Derrick AbbeyDerrick Abbey
You could just order them in your query. 
SELECT Id, Name, {Any other fields like date and letter}
FROM childObject
WHERE parent = :parentId
ORDER BY LetterField DESC, DateField DESC

 
mukesh guptamukesh gupta
Hi Phuc,

Please follow below code:-
  
List<AggregateResult> contactAggrList = [SELECT AccountId, max(dateField__c) 
                                                 FROM Contact 
                                                 Where AccountId =: accList                                                
                                                 ORDER BY dateField__c DESC];



if you need any assistanse, Please let me know!!

Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh
This was selected as the best answer
Suraj Tripathi 47Suraj Tripathi 47
Hii Phuc Nguyen,
Please follow below query :- 

List<AggregateResult> contactAggList = new List<AggregateResult>();
contactAggList  = [SELECT AccountId, max(date__c) FROM Contact Where AccountId IN : accountIds ORDER BY date__c DESC];

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks and Regards,
Suraj
Tarik ...Tarik ...

Hi All :)
What would be the piece o code if I have the same request without needing the order?

Actually, I just need to roll up the oldest (so the max) date on child records to the Contact object.
Thanks a lot!