How to Write SOQL for Distinct of combination of Level_1__c,Level_2__c and Level_3__c

Question

I would like to know if there is possibility of writing a SOQL similar like we do in SQL like Distinct of Level_1__c,Level_2__c and Level_3__c

Currently SOQL is

              select Case__c, Level_1__c, Level_2__c,Level_3__c  FROM ERT_Case_Type__c

How to rewrite to include Distinct of Level_1__c,Level_2__c and Level_3__c

Thanks & Regards, Carolyn

score 0 · Answer 1 · answered Nov 13 '20 at 16:17

I think for your use case, you will need Apex. Something like the following:

Map<String, Set<String>> distinctLevels = new Map<String, Set<String>>{
    'Level_1__c' => new Set<String>(),
    'Level_2__c' => new Set<String>(),
    'Level_3__c' => new Set<String>()
};
Set<String> levelApiNames = new Set<String>{'Level_1__c', 'Level_2__c', 'Level_3__c'};
for(ERT_Case_Type__c caseType : [SELECT Case__c, Level_1__c, Level_2__c, Level_3__c FROM ERT_Case_Type__c]){
    for(String level : levelApiNames){
        distinctLevels.get(level).add(caseType.get(level));
    }
}
for(String level : levelApiNames){
    System.debug('Number of distinct ' + level + ' values: '  + String.valueOf(distinctLevels.get(level).size());
    //System.debug('List of distinct ' + level + ' values: ' + distinctLevels.get(level).toString());
    //System.debug(distinctLevels.get(level).size()); //as integer
}

Loop over query results
Loop over each field API name you want to track
Add the values for each field to the set inside the map. The set will only have unique values
Do something with the map values for each field API name

This is not tested, but the high-level outline should work for you.

How to Write SOQL for Distinct of combination of Level_1__c,Level_2__c and Level_3__c

1 Answers1