0

I have the object Animal__c, with the field ZooId.

I want to make an SOQL Query with the animals sorted by CreatedDate DESC and Zoo Id.

Now the list is:

  • Animal 0 ZooId 5 CreatedDate Yesterday
  • Animal 1 ZooId 4 CreatedDate Today
  • Animal 2 ZooId 4 CreatedDate Yesterday
  • Animal 3 ZooId 5 CreatedDate Today
  • Animal 4 ZooId 5 CreatedDate Yesterday

SELECT Id, CreatedDate, ZooId ORDER BY CreatedDate DESC, ZooId

  • Animal 3 ZooId 5 CreatedDate Today
  • Animal 1 ZooId 4 CreatedDate Today
  • Animal 0 ZooId 5 CreatedDate Yesterday
  • Animal 4 ZooId 5 CreatedDate Yesterday
  • Animal 2 ZooId 4 CreatedDate Yesterday

And I want this:

  • Animal 3 ZooId 5 CreatedDate Today
  • Animal 0 ZooId 5 CreatedDate Yesterday
  • Animal 4 ZooId 5 CreatedDate Yesterday
  • Animal 1 ZooId 4 CreatedDate Today
  • Animal 2 ZooId 4 CreatedDate Yesterday

Is that possible?? Sorting like this With an SOQL Query?? Without using GROUP BY because I am not interested in aggregate results. My priority is **

  • first sorting by CreatingDate.
  • second sorting by ZooId.

**

Improve this question
1
  • The order you're describing here is actually the reverse of what you initially said. Your target sort order is first by ZooId, then by CreatedDate
    – Derek F
    Jan 14, 2020 at 18:23

2 Answers 2

Reset to default
5

Generally speaking, when I have a question, the first place I look is at the documentation.

Ordering by multiple columns is listed in ORDER BY in the SOQL & SOSL documentation (emphasis mine)

The following factors affect results returned with ORDER BY:

  • Sorting is case insensitive.
  • ORDER BY is compatible with relationship query syntax.
  • Multiple column sorting is supported, by listing more than one fieldExpression clause.

I don't think there's anything in the documentation that explicitly says that you can specify a different sort order per field, but it is indeed possible.

ex. [SELECT Id, Name, Site, CreatedDate FROM Account ORDER BY Site DESC NULLS LAST, CreatedDate ASC LIMIT 100]

Beyond that, the key here is to realize the order in which you need to do the sorting.

The sorting is applied from the leftmost statement, and proceeds right. The end effect is that the frequency at which the sorted fields change in your result also depends on the order in which they were sorted. The first thing that's sorted has the lowest frequency of change in the sorted result, and the last thing that is sorted will (can is probably a better word here) have the highest frequency of change.

Given your target result

  • Animal 3 ZooId 5 CreatedDate Today
  • Animal 0 ZooId 5 CreatedDate Yesterday
  • Animal 4 ZooId 5 CreatedDate Yesterday
  • Animal 1 ZooId 4 CreatedDate Today
  • Animal 2 ZooId 4 CreatedDate Yesterday

ZooId transitions once (5 -> 4), whereas CreatedDate transitions 3 times (Today -> Yesterday, Yesterday -> Today, Today -> Yesterday).
Therefore ZooId must be sorted first, and CreatedDate sorted second.

Improve this answer
2
  • 2
    It is possible that the nth sorted field can transition less than the (n-1)th sorted field in practice. If all of your Animal records were created at the same time, there would be little/no variance (and thus little/no transitions). What I'm trying to get at is that we can think of the sorting as being done successively. The first sort criteria is applied, and then the second criteria is applied within each bucket from the first sort, and so on.
    – Derek F
    Jan 14, 2020 at 19:35
  • great answer👌👌
    – Rahul Gawale
    Jan 15, 2020 at 0:52
2

Yes, you need to order like below

SELECT Id, CreatedDate, ZooId ORDER BY ZooId DESC, CreatedDate DESC
Improve this answer
1
  • 1
    Also, the DESC keyword is needed to sort in descending order. The default sort order is ASC (ascending).
    – Derek F
    Jan 14, 2020 at 18:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .