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
NoamNoam 

Getting min, max recored from a child list

Hey All,

Im trying to get the MIN and MAX dates from a related child list in soql.
For example if i have an account list with an Opportunity as a child object:

select Id, Name, (select CloseDate from Opportunities) from Account

ive tried this:
select Id, Name, (select Min(CloseDate) from Opportunities) from Account
 
and "​only root queries support aggregate expressions"

ive tried this:
select Id, Name, (select CloseDate from Opportunities order by CloseDate asc limit 1),  
(select CloseDate from Opportunities order by CloseDate desc limit 1) from Account
 
but of courde: "​Cannot follow the same aggregate relationship twice"

so my last try was doing like this: 
select Id, Name, (select CloseDate from Opportunities order by CloseDate asc) from Account

and then on vf page the min date will be:  AccList.Opportunities[0].CloseDate
and the max date will be: AccList.Opportunities[child list size].CloseDate

and there is the problem:  how can i get the child list size??

Any help or other elegant solution will be appreciated!

P.S
replace between the parent-child (meaning (select CloseDate from Opportunities where Id in: (account list))) is not an option!!!
(Although i know it will solve the problem)
kevin lamkevin lam
If you have the list of Account IDs, will this work for you?

SELECT AccountId, MAX(CloseDate) FROM Opportunity WHERE AccountId IN :accountIDs GROUP BY AccountId
BalajiRanganathanBalajiRanganathan
I would say if you are using Master Detail relationship then define two rollup summary fields in the parent to find min and max dates. in this way you  dont need to write a code to get the fields.
For your third approch, you will have problem if an account does not have any opportunities.
You can use Two SOQL (one to get min) and One to get Max, and then use A Wrapper class to show the details in VF
Class AccountWrapper{
     Account account;
     Opportunity minClose;
     Opportunity maxClose;
}
 
claperclaper
to get the child list size do this:  
List<Account> AccList = [Select Id, Name, (select CloseDate from Opportunities order by CloseDate asc) from Account];
//this map will contain each account.Id with the count of opportunities related to it
Map<Id,Integer> OpportunityCountByAccountId = new Map<Id,Integer>();
for(Account a : AccList)
{
      OpportunityCountByAccountId.put(a.Id,a.Opportunities.size());
}

alternatively you can do this
  
List<Account> AccList = [Select Id, Name, (select CloseDate from Opportunities order by CloseDate asc) from Account];
//where 'n' is the index of an account in the list
AccList[n].Opportunities.Size();

 
NoamNoam
kevin lam - that will not help me. thx anyway

balaji r - you are right, i will have that problem when account does not have opps. but this is thing - i need an account list with opps and not opp list.
i will go with one of your two options. Its not the most elegant thing but i think its the only solutions.

claper - thank for your suggestion. as balaji r said i will have problem with accounts without opps (null exeption on vf page) so this option is off.

Thank you all for your help!