+ Start a Discussion
Andrew MattieAndrew Mattie 

SOQL subquery now returning limited and unsorted data

I have a SOQL query that hasn't changed in a long time that look something like this:
SELECT Lead.Id, Lead.Name (SELECT Task.Id, Task.ActivityDate FROM Lead.Tasks ORDER BY Task.ActivityDate DESC) FROM Lead WHERE Id = 'xxx'
In the past, that has always returned the specified lead info as well as all tasks they have, or at least as many as the API allowed for, sorted by most-recent activity / due date. At some point recently, that query started returning up to 15 tasks that are completely unsorted. I don't care so much about the limited number of tasks being returned, but the seemingly broken sorting behavior is causing problems for us.

Any ideas?
Best Answer chosen by Andrew Mattie
kevin Carotherskevin Carothers
Yeah - I ran the above SOQL and the return events are descending...  
Granted - this was using force.com explorer -- Are you getting your results back from some other means?  

User-added image


I also tried in the SOQL workbench;

User-added image


All Answers

kevin Carotherskevin Carothers

Hi Andrew,

Well, other than a missing comma, your SOQL query seems to work like you want it to.
IDK what appens if you don't have a due date (ActivityDate) -- it might not list them in the order created.

SELECT Lead.Id, Lead.Name, 
    (SELECT Task.Id, Task.Subject, Task.ActivityDate 
     FROM Lead.Tasks 
     ORDER BY Task.ActivityDate DESC) 
FROM Lead WHERE Lead.Id = '00Qi000000DwfzN'


Andrew MattieAndrew Mattie
Ah, the missing comma came from me simplifying the query for this post.

The problem I have is that the due dates DO exist in the tasks that come back. The query results just aren't sorted by those dates any more. If you run that query on arbitrary leads in your own data set, do you see the tasks from the subquery come back sorted by newest first?
kevin Carotherskevin Carothers
Yeah - I ran the above SOQL and the return events are descending...  
Granted - this was using force.com explorer -- Are you getting your results back from some other means?  

User-added image


I also tried in the SOQL workbench;

User-added image


This was selected as the best answer
Andrew MattieAndrew Mattie
Kevin, thanks so much for verifying this for me. After playing around with it a little more, I realized my problem was due to a) nulls coming up first when I generally hadn't seen nulls for that field and b) people adjusting the due date for existing tasks when they generally don't do that in our data.

Anyway, thank you again for your time and help. Problem solved.