I use Cosmos Db and I need results to be sorted by the results of a COUNT.
Instead of sorting the results each time myself (or create a service for it), I prefer having the results sorted directly in the output of the query from Cosmosdb.
In Document Explorer of Azure, I execute the following request on the AggregatedEvent collection:
SELECT * FROM (
SELECT COUNT(1) AS AlarmCount, a.AlarmType FROM AggregatedEvent a
WHERE a.AlarmType != null and a.Prom > 0
GROUP BY a.AlarmType ) AS g
ORDER BY g.AlarmCount
It results the following error message:
Identifier 'AlarmCount' could not be resolved.
If I use another property for ordering g.AlarmType, a property that exists in the document, then the ordering is performed.
Please add an answer only if you have a way to archieve the ordering with CosmosDb.
As of January 2025, this functionality is still not supported. The documentation from Microsoft defines the limitation here: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-group-by
The GROUP BY clause does not allow any of the following:
- Aliasing properties or aliasing system functions (aliasing is still allowed within the SELECT clause)
- Subqueries
- Aggregate system functions (these are only allowed in the SELECT clause)