Query in cosmos db is not working

Question

hello I have the following query
SELECT device.Latitud, device.Longitud FROM device WHERE device.id IN (SELECT MAX(device.id) FROM device GROUP BY device.IMEI )

but ists no working if ai took the sentence SELECT device.Latitud, device.Longitud FROM device, it works and SELECT MAX(device.id) FROM device GROUP BY device.IMEI works

but i need the complete query

Answer 1

Hi @Gabriel Velandia , welcome to Microsoft Q&A forum.

We need to refer to article to understand how sub-queries work in Azure Cosmos DB:
Correlated: A subquery that references values from the outer query. The subquery is evaluated once for each row that the outer query processes.
Non-correlated: A subquery that's independent of the outer query. It can be run on its own without relying on the outer query.

Azure Cosmos DB supports only correlated subqueries.

The sub-query mentioned the question is a non-correlated subquery.

We need to run the sub query first and then that result can be used as an array and fire the second query individually.

You can refer to below articles that mention the same behavior:

Azure cosmos DB correlated subquery not working as expected

Azure CosmosDB nested WHERE query

Please let us know if this helps.

----------

If answer helps, you can mark it 'Accept Answer'