I have an object called SRC which has account as lookup in it. SRC content will be as shown below:
SRC is created populating the BillingCity from Account at that point of time. So everytime BillingCity is changed in account there will be a new SRC created.
I want latest SRC record of an account. The output is as shown below:
I ideally want to write a query like this:
SELECT Status FROM SRC__c WHERE Account__c IN ('1', '2') AND Account__r.BillingCity = City__c GROUP BY Account__r.Id ORDER by LastModifiedDate DESC LIMIT 1
How can I achieve this? I cannot run the query inside a for loop, because I am writing this code in a trigger.
I have two problems here:
- Account__r.BillingCity = City__c is not allowed
- using group by gives me "Ordered field must be grouped or aggregated" error