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!