Salesforce equivalent of "SELECT DISTINCT"?

Question

I want to know what all the existing values of a field are.

In SQL that would be something like:

SELECT DISTINCT foo
FROM bar

How might I do this with salesforce's SOQL?

Prune

Answer 1

Use Group by

SELECT foo
FROM bar
GROUP BY foo

eg:

SELECT Status
FROM Case
GROUP BY Status

1|New
2|Assigned
3|In Progress
4|In Progress Known Issue

Prune

Answer 2

In other case, you can using:

SELECT Id, foo, col_Sort, another_multiple_col
  FROM bar
ORDER BY foo, col_Sort

After that, You can using Map with key: "foo". Add all data query to this map. So you will have the distinct record. Just using

yourmap.get('your_key').

to get result

Stagecoach

Answer 3

While using Group By, try to use COUNT(ID) also.

Case #1:

List<AggregateResult> lst1 = [SELECT foo FROM bar GROUP BY foo];

Though if this query returns just 15 records after processing 10000 records, all the records it processed to get the AggregateResult will be counted against the 50000 SOQL rows Governor Limit.

Number of query rows: 10000 out of 50000

Case #2:

List<AggregateResult> lst2 = [SELECT COUNT(ID), foo FROM bar GROUP BY foo];

In this case, only the final result rows (that is 15) will be counted against the 50000 SOQl rows Governor Limit.

Number of query rows: 15 out of 50000

Homomorphism