I have an object called SRC which has account as lookup in it. SRC content will be as shown below:
SRC ID | Account__c | Status | City__c | LastModifiedOn |
---|---|---|---|---|
1 | 1 | Yes | Delhi | 12-Apr |
2 | 1 | No | Cochin | 13-Apr |
3 | 2 | No | Delhi | 14-Apr |
4 | 2 | Yes | Indore | 15-Apr |
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:
SRC ID | Account__c | Status | City__c | LastModifiedOn |
---|---|---|---|---|
2 | 1 | No | Cochin | 13-Apr |
4 | 2 | Yes | Indore | 15-Apr |
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