Multiple Order statements do not seem to work

[gvc55]

Not sure if I am missing something but when I issue a query with multiple ORDER statements, only the first one is acknowledged. If I do this is SQL4CDS, it works as expected. But then converting the SQL query to FetchXml and executing in FetchXml Builder, the results are not the same. Any ideas will be greatly appreciated.

UPDATE: It appears that this is NOT a FetchXml Builder issue but FetchXml in general. Does anyone know if multiple ORDER statements are allowed?

[MarkMpn]

Depends on the details of the query. Generally, if you are trying to apply sorts on a link-entity, SQL 4 CDS will choose to not include that sort in the FetchXML and apply it itself as a separate process as it can lead to problems with paging.

Multiple orders are allowed, and they are applied "outside-in", so any sorts on the outer-most entity are applied first, followed by those on the link-entities. There's more information on my blog

Sorting on link-entities forces Dataverse to use legacy paging which limits the results to 50,000 records, so be cautious if you're expecting more than that.

[gvc55]

Thanks for your reply, Mark! But from what I can tell (and perhaps you can as well), this is a simple query (no linked entities involved) so maybe the placement of the order tag is the issue? No matter what I do to the order of the ORDER tags, the result is always using the last order tag. The first one is not considered at all. Same result using FetchXml from code.

<fetch>
  <entity count="10000" name="audit">
    <filter type="and">
      <condition operator="in" attribute="action">
        <value>1</value>
        <value>2</value>
      </condition>
      <filter type="or">
        <condition operator="like" attribute="attributemask" value="%82%" />
        <condition operator="like" attribute="attributemask" value="%77%" />
        <condition operator="like" attribute="attributemask" value="%90%" />
      </filter>
      <condition operator="lt" attribute="createdon" value="2023-10-13 16:42:19Z" />
      <condition operator="in" attribute="objectid">
        <value>f9128b22-2a85-ec11-8d20-001dd804dd6f</value>
        <value>e2462ad8-2885-ec11-8d20-001dd804b8ee</value>
        <value>471aadd2-2a85-ec11-8d20-001dd804dd6f</value>
        <value>61168e84-2585-ec11-8d20-001dd804dd6f</value>
        <value>035796a4-2685-ec11-8d20-001dd804dd6f</value>
        <value>063e9b90-2585-ec11-8d20-001dd804dd6f</value>
        <value>90e68530-2685-ec11-8d20-001dd804b8ee</value>
        <value>e26a1816-2485-ec11-8d20-001dd804dd6f</value>
        <value>ebd609ce-2385-ec11-8d20-001dd804dd6f</value>
      </condition>
    </filter>
    <order attribute="objectid" descending="false" />
    <order attribute="createdon" descending="true" />
  </entity>
</fetch>

[MarkMpn]

Ah, you’ve found one of the wonderful oddities of the audit entity. There are many things that are unusual about how you can or cannot query this entity, and this is one of them.

For most entities, adding two sorts as you have done is fine. For elastic tables you’ll get an error saying only a single sort is supported. audit is backed by the same Cosmos DB store as elastic tables but instead of producing an error simply ignores any extra sorts.

[gvc55]

WOW! That is odd! Thanks for the explanation. Kind of makes sense but without any hint of why it fails, it is hard to understand what is going on. Thanks again.