How to select Unique values in SOQL distinct?

2.3K Asked by darsh_6738 in Salesforce , Asked on May 3, 2023

I am trying to get unique values by Territory_ID__c with a (Salesforce SQL) SOQL query.

I tried this:

return [select id,name,ANNUAL_CALLS__c,city__c,state__c,No_Of_Targets__c, Territory_ID__c,Territory__r.name,Territory__r.ANNUAL_CALLS__c from zip__c where name in :sArr unique by Territory_ID__c];

But this does not return the unique values. How can I get unique values of a column?

Answered by Dipesh Bhardwaj

An alternative to selecting soql distinct rows:

SELECT name, COUNT(Id) FROM Lead GROUP BY name

Shows you all of the distinct names, with how many of them there are.

One drawback is if there are too many rows in the table, it will just fail, just like sales force is in an existential way.

Your Answer

Answers (2)

Ranjana

If you're trying to select unique values in SOQL, you might be wondering if there’s a DISTINCT keyword like in SQL. Unfortunately, SOQL doesn’t support SELECT DISTINCT directly, but you can still get unique values using workarounds!

1. Why Doesn't SOQL Support DISTINCT?

Unlike SQL, SOQL is designed for Salesforce’s multi-tenant architecture.
Salesforce limits queries to specific fields and objects to optimize performance.

2. Workarounds to Get Unique Values

Method 1: Use GROUP BY (Best for Unique Field Values)
If you want distinct values for a single field, use GROUP BY:

  SELECT Name FROM Account GROUP BY Name

This returns unique account names.
Works only for aggregatable fields (text fields work, but not picklists).

Method 2: Use Aggregate Functions (COUNT() or MAX())

To count unique values:

  SELECT Name, COUNT(Id) FROM Account GROUP BY Name

Retrieves unique names with the number of occurrences.

Method 3: Use Set<> in Apex (Best for SOQL Query Results)

If SOQL returns duplicates, filter them using Apex:

Set uniqueNames = new Set();

for (Account acc : [SELECT Name FROM Account]) {

    uniqueNames.add(acc.Name);

}

System.debug('Unique Account Names: ' + uniqueNames);

The Set<> automatically removes duplicates.

3. Final Thoughts

Use GROUP BY for distinct field values.
Use COUNT() if you need a count of unique entries.
Use Apex Set<> to remove duplicates after querying.

2 Weeks

Ranjana

In Salesforce Object Query Language (SOQL), you can select unique values using the DISTINCT keyword. This is particularly useful when you want to retrieve unique records from a database table based on specific fields. Here’s how you can use the DISTINCT keyword in SOQL:

Basic Usage of DISTINCT

To select unique values in SOQL, you simply add the DISTINCT keyword right after the SELECT keyword. Here is a basic example:

  SELECT DISTINCT Name FROM Account

This query retrieves all unique account names from the Account object.

Important Considerations

Field Limitations: The DISTINCT keyword can only be used with a single field or a combination of fields, but it doesn’t work with formula fields or fields from related objects.

Performance: Using DISTINCT can impact performance, especially on large datasets. Make sure to use it judiciously.

Null Values: DISTINCT considers null values as unique, so if there are multiple records with null values in the selected field, each null value will be treated as unique.

Selecting Multiple Fields

You can use DISTINCT with multiple fields to get unique combinations of those fields. For example:

SELECT DISTINCT Name, Industry FROM Account

his query retrieves unique combinations of Name and Industry from the Account object.

Example with Filter and Sorting

You can combine DISTINCT with filtering and sorting clauses:

10 Months