How to select Unique values in SOQL distinct?

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.
  

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: