function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SF_RocksSF_Rocks 

SOQL Grouping and Ordering in same statement

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?

 

logontokartiklogontokartik
Hi,

You need to add all the fields you are selecting to your Group By in SOQL statement for your SOQL to work. Please add Order__c field also to your Group BY and see if it works. 

Thank you
Vinit_KumarVinit_Kumar
Try below query :-

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

Any field thats included in Aggregate SOQL has to be either aggregated or grouped that's the key here.

Hope this helps !!
SF_RocksSF_Rocks
Thank you both for your suggestions, however neither worked for some reason.  For example, when I use Vinit's suggestion I get the following Error:

MALFORMED_QUERY:
'0064000000Rw0ea' GROUP BY Group__c,Order__c,OpportunityId ORDER BY
                                    ^
ERROR at Row:1:Column:110
field 'Order__c' can not be grouped in a query call

Order__c is type=number
Vinit_KumarVinit_Kumar
Ohk just saw you have already aggregated your Order__c field so remove it from Group by and then try something like below :- 
SELECT MAX(Order__c), Group__c FROM OpportunityLineItem WHERE OpportunityId = '0064000000Rw0ea' GROUP BY Group__c,OpportunityId ORDER BY Order__c
Hope this helps !!
F SmoakF Smoak
I am stuck in a similar requirement where I have to find the list of calls created in last 7 days for accounts. I should be able to get results as for each account latest created call , ie 1 call per account created latest in last 7 days. I have written the query as :
like if say we have c1 created on a1 on 25/09
c2 created on a1 on 30/09
c4 created on a2 on 27/09

my result set should return:
c2 created on a1 on 30/09
c4 created on a2 on 27/09

SELECT Account__c,DateTime__c FROM Call__c group by Account__c,DateTime__c  ORDER BY DateTime__c DESC NULLS LAST LIMIT 1 
I am getting same error AS: 
MALFORMED_QUERY:
Call2__c group by Account__c,DateTime__c ORDER
^
ERROR at Row:1:
field 'DateTime__c' can not be grouped in a query call

any idea how to achieve this data in the single query?
F SmoakF Smoak
I have modified something like :

But I dont have my record identifier so that I can use the id field to access other fields of record in further list collections,
SELECT MAX(DateTime__c),Account_vod__c FROM Call__c where Status__c = 'Submitted_vod' AND DateTime__c >= LAST_N_DAYS:7 and Accountc !=null  group BY Accoun__c