How to select Unique values in SOQL distinct?

1.9K 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

Answer (1)

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:

6 Months