Salesforce SOQL Distinct Queries [How to Select DISTINCT values in SOQL?]

In other languages, a Distinct query returns only the unique values. This feature of the DISTINCT keyword is not supported in SOQL queries for finding unique and duplicate values. In this Salesforce SOQL tutorial, I will explain how to execute the alternate method for the DISTINCT keyword in SOQL to retrieve unique count values from query results.

Table of Contents

How to count the Unique values in Salesforce SOQL?

Unlike SQL, SOQL does not support the DISTINCT keyword. SOQL is designed to be simple and efficient when querying Salesforce data. The lack of DISTINCT support is due to its limited focus compared to SQL. In Salesforce, we have other ways to get a unique count of the records, such as creating a report with unique counts of records.

Select DISTINCT (unique) values in Salesforce SOQL

Now, we will see the examples through which we will get the unique values of a field value in a Salesforce object.

Example 1 – Let’s take an example of finding unique values of field values from an object. In the below SOQL query, we will find the unique count of the ‘Account Types‘ from the Account object.

SELECT Type FROM Account GROUP BY Type

How to find Distinct count in Salesforce SOQL query

Example 2 – Let’s take another example where the query counts how many leads exist for each unique Name in the Lead object.

SELECT count(Id), Name FROM Lead GROUP BY Name

To access this unique count functionality in Apex code, we can write the code as follows:

AggregateResult[] results = [SELECT COUNT(Id) countId, Name FROM Account GROUP BY Name];
List<String> uniqueNames = new List<String>();
for (AggregateResult result : results) {
    uniqueNames.add((String)result.get('Name'));
}
System.debug(uniqueNames);

The above method declares a List called uniqueNames to store the unique account names. Then, it
Iterates over the AggregateResult list (results), extracting the Name field from each record and adding it to uniqueNames.

In the output, it logs the uniqueNames list, which contains all unique account names retrieved by the SOQL query.

If you want the output in Set instead of List, follow the code below.

AggregateResult[] results = [SELECT COUNT(Id) countId, Name FROM Account GROUP BY Name];
Set<String> uniqueNames = new Set<String>();
for (Account p : [SELECT Name FROM Account]){
uniqueNames.add(p.Name);
}
System.debug(uniqueNames);

In this way, we can get the unique count of field values in Salesforce Apex code and SOQL by following the above methods.

Return Distinct Records With The Named Query Feature

In Salesforce SOQL, returning distinct records can be achieved using the GROUP BY clause with Named Queries. While there isn’t a direct SQL-style DISTINCT keyword in SOQL, the GROUP BY combined with aggregate functions like COUNT or MIN gives a similar output.

In the SOQL query below, we will retrieve aggregated data from the account object with specific grouping and limit the results to 10 records.

SELECT Count_DISTINCT(ID) cid, Account.Name, Account.Industry, Account.Type
FROM Account
GROUP BY Account.Name, Account.Industry, Account.Type
LIMIT 10

Output:

This way, we can return DISTINCT records with their unique counts using the Named query feature.

Return Distinct Records With LineItems SOQL

In Salesforce, to retrieve distinct records with their related line items using SOQL, we need to use an aggregate query imposed upon relationship queries.

Below is an example of a SOQL query that retrieves distinct records of Opportunity objects with line items.

SELECT OpportunityId, COUNT(Id)
FROM OpportunityLineItem
GROUP BY OpportunityId

Output:

Count Distinct values in Salesforce SOQL

Conclusion

While Salesforce SOQL does not support the DISTINCT keyword like SQL, it provides alternative methods to retrieve unique values or counts through the ‘GROUP BY‘ clause and aggregate functions. Following the above methods, you can retrieve the records and perform unique value calculations effectively.

Apart from this feature, if you just want to get the unique count of the field values in a specific object, then for that purpose, you should use the Salesforce reports, which have a built-in feature for counting unique field values.