[SalesForce] How to get unique values of two fields in one SOQL

I have a custom object named "Performance" in which there are two text fields named "Main Category" and "Sub Category". Here, I would like to first extract the unique values of Main Category from all Performance records, then extract the unique Sub Category values from each Main Category.

Example Performance object table:

Rec1: MC1 – SC1

Rec2: MC1 – SC2

Rec3: MC2 – SC2

Rec4: MC2 – SC3

Rec5: MC3 – SC4

Rec6: MC4 – SC5

Rec7: MC4 – SC5

I would like to obtain information as follows:

Unique MC value: MC1, and its unique sub categories: SC1, SC2

Unique MC value: MC2, and its unique sub categories: SC2, SC3

Unique MC value: MC3, and its unique sub categories: SC4

Unique MC value: MC4, and its unique sub categories: SC5

I'm aware that the "easy" way get this information is by doing an Aggregate SOQL (using GROUP BY) for obtaining unique main categories, then doing a bunch of Aggregate SOQL for obtaining unique sub categories for each resultant unique main categories obtained from previous SOQL. However I do not know how many Main Categories there will be, thus putting me in danger of reaching SOQL query governor limit.

Is there any way to obtain such information from just one advanced SOQL, or any other method which will not push governor limits?

Best Answer

Sure, just group by both.

SELECT Main__c, Sub__c
FROM MyObject__c
GROUP BY Main__c, Sub__c

This query will give you all the combinations. Note that without a where clause, it pulls every record in the table so the query rows limit might be a concern.

Related Topic