I am stuck on writing a query to facilitate Congra Composer.  For reasons beyond this request, I need to 'dedupe' a query of data as well as put it into a set order.  I can easily do one or the other but not both.  If I do an ORDER BY I get all results using the following SOQL:

SELECT Group__c, Order__c FROM OpportunityLineItem WHERE Opportunity.Id = 'xxxxxxxxxxxx' Order by Order__c

Which returns:

1   Eggs
1   Eggs
2   Tomatoes
2   Tomatoes
3   Bananas
3   Bananas

However that does not allow me to 'dedupe' as i would if I used the following SOQL:

SELECT Group__c FROM OpportunityLineItem WHERE Opportunity.Id = 'xxxxxxxxxxx' GROUP BY Group__c

Which returns:

Eggs
Tomatoes
Bananas

So... When I put it all together in hopes of getting (1 Eggs, 2 Tomatoes, 3 Bananas) with the following SOQL:

SELECT MAX(Order__c), Group__c FROM OpportunityLineItem WHERE Opportunity.Id = '0064000000Rw0ea' GROUP BY Group__c ORDER BY Order__c

I get various "MALFORMED_QUERY: Ordered field must be grouped or aggregated: Order__c" Errors

Any ideas on how to achieve an ordered and distinct (deduped) query?

 

I am stuck on writing a query to facilitate Congra Composer.  For reasons beyond this request, I need to 'dedupe' a query of data as well as put it into a set order.  I can easily do one or the other but not both.  If I do an ORDER BY I get all results using the following SOQL:

SELECT Group__c, Order__c FROM OpportunityLineItem WHERE Opportunity.Id = 'xxxxxxxxxxxx' Order by Order__c

Which returns:

1   Eggs
1   Eggs
2   Tomatoes
2   Tomatoes
3   Bananas
3   Bananas

However that does not allow me to 'dedupe' as i would if I used the following SOQL:

SELECT Group__c FROM OpportunityLineItem WHERE Opportunity.Id = 'xxxxxxxxxxx' GROUP BY Group__c

Which returns:

Eggs
Tomatoes
Bananas

So... When I put it all together in hopes of getting (1 Eggs, 2 Tomatoes, 3 Bananas) with the following SOQL:

SELECT MAX(Order__c), Group__c FROM OpportunityLineItem WHERE Opportunity.Id = '0064000000Rw0ea' GROUP BY Group__c ORDER BY Order__c

I get various "MALFORMED_QUERY: Ordered field must be grouped or aggregated: Order__c" Errors

Any ideas on how to achieve an ordered and distinct (deduped) query?