How to get recently created child object in one soql

Question

I have 2 objects, Portfolio__c and batch__c. We can create batches against portfolio (i.e) batch__c has a lookup field to Portfolio__c. I have a VF page which was showing a list of portfolios and the latest batch created for that portfolio. The VF page uses a repeater which is bound to a list of Wrapper class. The logic I use for filling the wrapper list is shown below.

foreach portfolio
{
 Wrapper wrp = new Wrapper();
 wrp.portfolioId=....;
 wrp.CreatedDate = (Datetime)([select max(createddate) maxdate from Batch__c  where Portfolio__r.Id != null and Portfolio__r.Id =: wrp.portfolioId][0].get('maxdate'));
 lstBatch = [select Id,Name,Status__c, (SELECT Asset__r.Id FROM BatchItem__r) from  Batch__c where Portfolio__r.Id =: wrp.portfolioId and CreatedDate =: wrp.CreatedDate ];    
.
.
.
lstWrapper.Add(wrp);         
}  

Is there a way available to get most recently created batch and its details in one soql query?

Answer 1

You can use a subquery -

[Select Id, ..(Select Id from Batch__c order by createdDate DESC) from Portfolio__c]

Or else you can follow same process which you are using above and order by PortfolioId and createdDate

select Id,Name,Status__c, (SELECT Asset__r.Id FROM BatchItem__r) from  Batch__c where Portfolio__r.Id =: wrp.portfolioId Order By ProtfolioId, CreatedDate DESC