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?