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
Shrey TyagiShrey Tyagi 

SOQL Order By Clause - Need help with Syntax

Hi Everyone ,
        I have this query and it works well. 

select Id,Name,StageName,Sub_Stage__c,Opportunity_ID__c,Account.Name,Proposal__r.Name,(Select Project__c From Awarded_Projects__r where Name != null Limit 1) from Opportunity where Name!= null order by Name asc limit 30

Now I want to order this query by  Project __c field that is fetched from the Child Record of :Awarded_Project__r. Can anyone please help me with the syntax?


Thanks 
Shrey Tyagi
 
JeffreyStevensJeffreyStevens
No - that wouldn't make sense.  You would have a one->many relationship between Opportunity and Awarded_Projects__c.  So, if you have multiple Awarded_Projects__c records - when copy of Project__c would you order by?
Amit Chaudhary 8Amit Chaudhary 8
You can try like below 
select name,(select firstname from contacts order by firstname ) from account order by Name

Your code should be like below 
select Id,Name,StageName,Sub_Stage__c,Opportunity_ID__c,Account.Name,Proposal__r.Name,(Select Project__c From Awarded_Projects__r where Name != null order by Project__c Limit 1) from Opportunity where Name!= null order by Name asc limit 30

Let us know if this will help you

 
Shrey TyagiShrey Tyagi
Jeffrey : I knew about that problem , but in my case I will have only 1 awarded project record /opportunity. That is why I have Limit 1 clause as well.

Amit: I want to sort the outer query by project number not the inner query

Something like this:

select Id,Name,StageName,Sub_Stage__c,Opportunity_ID__c,Account.Name,Proposal__r.Name,(Select Project__c From Awarded_Projects__r where Name != null Limit 1) from Opportunity where Name!= null order by Awarded_Project__r.Project__c asc limit 30
JeffreyStevensJeffreyStevens
If you know that there is a one-to-one relationship - a couple of things you might try...

Is Project__c a text field?  Or is it a lookup to another sObject?

If it's a normal field, and if the Opportunity->Awarded_Projects__c is a Master-Detail relationship - then you could do a roll-up summary field on Opportunity of MAX(Awarded_Projects__c.Project__c).  But - even then - I'm not sure if you can sort on a formula/roll-up summary field.  Would just have to try it.

If you can't do that - then you could always write a trigger on the Awarded_Projects__c object, and populate the Project__c field to the Opportunity.  Then you would have a field to sort on in the SOQL.