Getting Distinct Values in SOQL Query

Question

I need to provide a query that gives me the latest StageName from the Opportunity object sofar I have tried this by writing a query for OpportunityHistory and the OpportunityFieldHistory. The thing is that when an Opportunity goes through more than one stage update in one day we need to provide only the latest Stage update. Any help would be appreciated.

Answer 1

Query the latest StageName from the Opportunity object to use the OpportunityFieldHistory object, which tracks the history of changes for the StageName field. You can use a SOQL query that filters by the OpportunityId, the Field name, and the CreatedDate, and then sorts by the CreatedDate in descending order.

For example:

Id oppId = '0063h00000D4uP8AAJ'; // Replace this with the your OpportunityId
List<OpportunityFieldHistory> oppFieldHistories = [SELECT Id, OldValue, NewValue, CreatedDate
                                                   FROM OpportunityFieldHistory
                                                   WHERE OpportunityId = :oppId
                                                   AND Field = 'StageName'
                                                   ORDER BY CreatedDate DESC
                                                   LIMIT 1];

if (!oppFieldHistories.isEmpty()) {
        OpportunityFieldHistory oppFieldHistory = oppFieldHistories[0];
        String latestStageName = (String) oppFieldHistory.NewValue;
        System.debug(latestStageName);
}

This query will return the most recent change for the StageName field for a given OpportunityId. If you want to query the latest StageName for multiple OpportunityIds, you can use a list or a set to store the OpportunityIds and use the IN operator in the WHERE clause.

For example:

List<Id> oppIds = new List<Id>{'0063h00000D4uP8AAJ', '0063h00000D4uP9AAJ'}; // Replace this with the your OpportunityIds
List<OpportunityFieldHistory> oppFieldHistories = [SELECT Id, OpportunityId, OldValue, NewValue, CreatedDate
                                                   FROM OpportunityFieldHistory
                                                   WHERE OpportunityId IN :oppIds
                                                   AND Field = 'StageName'
                                                   ORDER BY OpportunityId, CreatedDate DESC];

Map<Id, String> oppIdStageNameMap = new Map<Id, String>();
for (OpportunityFieldHistory oppFieldHistory : oppFieldHistories) {
        Id oppId = oppFieldHistory.OpportunityId;
        String latestStageName = (String) oppFieldHistory.NewValue;
        if (!oppIdStageNameMap.containsKey(oppId)) {
                oppIdStageNameMap.put(oppId, latestStageName);
        }
}

for (Id oppId : oppIdStageNameMap.keySet()) {
        String latestStageName = oppIdStageNameMap.get(oppId);
        System.debug('OpportunityId: ' + oppId + ', Latest StageName: ' + latestStageName);
}

This query will return the latest StageName for each OpportunityId in the list or set, and store them in a map. You can then use the map to access the latest StageName for any OpportunityId.