SOQL: Get most recent amount grouped by a parent and a type

Question

Should be simple had it been SQL. But. I would like to get the amount field value that belongs to the most recent month available for that parent and that type.

Data:

Parent_Id__c    Type__c    Date__c       Amount__c
1               A          09/01/2016    100.00
1               A          10/01/2016    200.00
1               A          08/01/2016    50.50
1               B          09/01/2016    1000.90
1               B          07/01/2016    1100.40
1               C          05/01/2016    888.20
2               A          09/01/2016    100.40
2               A          10/01/2016    200
2               A          08/01/2016    50
2               B          09/01/2016    1000
2               B          07/01/2016    1100
3               C          05/01/2016    888

Desired Output:

1 A 200.00
1 B 1000.90
1 C 888.20
2 A 200
2 B 1000
3 C 888

What I have so far:

select Parent_Id__c, MAX(Date__c), Type__c from Expense__c 
GROUP BY Parent_Id__c, Type__c
ORDER BY Parent_Id__c, Type__c

But when I throw in Amount__c currency field in the mix, like so:

select Parent_Id__c, MAX(Date__c), Type__c, Amount__c from Expense__c 
GROUP BY Parent_Id__c, Type__c, Amount__c
ORDER BY Parent_Id__c, Type__c

...it says "field 'Amount__c' can not be grouped in a query call". Sounds like it is not possible to get what I want with pure SOQL (looked at this also). Can the experts comment/confirm please?

Answer 1

No, you cannot get the specific Amount__c given the maximum Date__c for your groupings. Not in one go, anyway. I think the path of least resistance would be to scrap the aggregation and just use post-processing.

You could do a mapping of combined key to Expense__c record, but it may be easier to just use a Map<Id, Set<String>> to track observed values and store the records in a List<Expense__c instead. You could use an inner class that looks something like:

public class ExpenseSorter
{
    public List<Expense__c> mostRecentExpenses { get; private set; }
    final Map<Id, Set<String>> observedValues;
    public ExpenseSorter()
        mostRecentExpenses = new List<Expense__c>();
        observedValues = new Map<Id, Set<String>>();

        for (Expense__c record : [
            SELECT Parent_Id__c, Date__c, Type__c, Amount__c
            FROM Expense__c ORDER BY Date__c DESC Parent_Id__c
        ]){
            if (!observedValues.containsKey(record.Parent_Id__c))
                observedValues.put(record.Parent_Id__c, new Set<String>{ record.Type__c });
            else if (!observedValues.get(record.Parent_Id__c).contains(record.Type__c))
                observedValues.get(record.Parent_Id__c).add(record.Type__c);
            else
                continue;
            mostRecentExpenses.add(record);
        }
    }
}

Or you could remove the query and accept the List<Expense__c> as a constructor parameter for added flexibility. The above is just an idea to give you the general outline of how you can solve your problem.