I have a data model where I have an Object, Software_License__c, which contains a Lookup to an Account. There are Attachments against the Software License. I can safely assume the Software License has one attachment, but a given Account may have multiple Software Licenses. The Software License contains a DateTime called Date__c.
I need to return the Attachment (with Body) of the most recent Software License given the ID of the Account lookup.
I've tried a few things.
SELECT Id , Date__c, (SELECT Id, Body ,Name FROM Attachments LIMIT 1) FROM Software_License__c WHERE Account__c = :CurrentUser.AccountId ORDER BY Date__c DESC
This does not work because you can't return binary data (e.g., the attachment body) from a subquery.
select Id, Body, Name from Attachment where ParentId in (select Id from Software_License__c where Account__c = :CurrentUser.AccountId ORDER BY Date__c DESC LIMIT 1 )
that doesn't work because you need to select
Date__c to ORDER BY it.
I can't think of a way to make this work with just one query. Anyone have a thought? TIA.