Using ORDER BY in Azure Cosmos DB Queries

      ORDER BY is a common syntax of T-SQL. You don't need to any special requirements to use it in SQL Server. This is not the case for Azure Cosmos DB. To use ORDER BY for a property, you need to be sure that the property is indexed in your CosmosDB Collection. If you try to use ORDER BY with an un-indexed property, you will get an error. You could not order by id either. You used to get the error "Order-by item requires a range index to be defined on the corresponding index path" when you try to use ORDER BY id in your queries.

     The main reason for this error was id is part of a composite index (id, partitionkey) already. Thanks to Azure Cosmos DB team, they fixed this problem and you should not get this error anymore when you try to ORDER By id anymore.
You still need to use ORDER BY with indexed properties.

     If you need to use multiple properties in your ORDER BY then you need to define COMPOSITE INDEXES.For example when I try to run the following query and try to order the objects by CreatedOn and Score, I end up with an error because I do not have a COMPOSITE INDEX to use with this ORDER BY.

SELECT * FROM c 
WHERE c.OwnerUserId = 1 
ORDER BY c.CreatedOn, c.Score

Failed to query item for container Posts: {"code":400,"body":{"code":"BadRequest","message":"Message: {\"Errors\":[\"The order by query does not have a corresponding composite index that it can be served from."\]

     To fix this problem; I need to add a composite index for CreatedOn ascending and Score ascending to my Indexing Policy. Be careful when you change your Indexing Policies in production. Any change will trigger Re-Indexing and until its done, some of your queries might not return all the data.

{ 

 "indexingMode": "consistent", 

 "automatic": true, 

 "includedPaths": [ 

    { 

     "path": "/*" 

    }

 ], 

 "excludedPaths": [ 

    { 

     "path": "/\"_etag\"/?" 

    }, 

    { 

     "path": "/PostBody/?" 

    } 

 ], 

 "compositeIndexes": [ 
    [ 
     { 
      "path": "/CreatedOn", 
      "order": "ascending" 
     }, 
     { 
      "path": "/Score", 
      "order": "ascending" 
     } 
    ] 
 ] 

}

     I can run the same query with no problem now. If you need to change the order from ascending to descending in your query. You need to create a new composite index or change your current one.