SOQL query to extract only the most recent attachment IDs

Question

I’m trying to create a SOQL query that returns attachment IDs from a custom object (Invoice__c) related to Account.

The tricky part is to extract only the most recent attachment from each Invoice__c. Please note, an Account may have more than one invoice object, each with several attachments.

I was able to create the following query:

Select Id FROM Attachment WHERE ParentId IN (SELECT Id from Invoice__c WHERE Account_ID__c = 'xxx')

But the problem is I’m getting ALL attachments, whereas I need only the most recent Attachment from each invoice. The result should be multiple Id’s, but not all.

If I add "ORDER BY Createddate LIMIT 1", then, as a result, I'm getting only 1 Id, which is not what's required.

Any advice would be much appreciated!

Answer 1

You need a subquery for this:

select (select id from attachments order by createddate desc limit 1) from invoice__c

The result will be a list of Invoice__c records, with a child record for each attachment:

for(Invoice__c invoiceRecord: results) {
  for(Attachment attachmentRecord: invoiceRecord.Attachments) {
    // most recent attachment for that invoice here
  }
}