CosmosDB - return list with Top 1 of each item in a list with many records-CodePudding

I have the following table:

Id | Plate | RecordedAt

For each different plate there can be N records.

I'm struggling with the following query:

 SELECT TOP 1 * 
 FROM table
 WHERE table.Plate IN ('plate1', 'plate2', 'plate3') 
 ORDER BY c.Recordedat DESC

The problem is that this query returns only 1 record with the last Recordedat item.

I need the last Recordedat of each item in the list.

Is there any way to achieve this with only one query?

For example for the following records:

Id  |     Plate    | RecordedAt
 1  |   Plate1     | 2021/09/26 6:53:06
 3  |   Plate2     | 2021/09/25 7:45:10
 4  |   Plate3     | 2021/09/23 02:10:42
 5  |   Plate1     | 2021/09/25 02:40:02
 6  |   Plate2     | 2021/09/26 15:14:02
 7  |   Plate1     | 2021/09/26 01:02:04
 8  |   Plate3     | 2021/09/26 16:02:20 
 9  |   Plate1     | 2021/09/24 05:02:20
 10 |   Plate2     | 2021/09/24 04:03:02

I need to return:

[
  {
    "Plate1":"2021/09/26 15:14:02"
  },
  {
    "Plate2":"2021/09/26 15:14:02"
  },
  {
    "Plate3":"2021/09/26 16:02:20"
  },
]

CodePudding user response：

It's not possible to do this type of query in Cosmos DB.

You will need to materialize the aggregate or value for each plate and then query for that.

CodePudding user response：

You can use GROUP BY to group on the plates. Then you can use aggregate functions on the other columns. In this case you want the last/highest date so you can use MAX:

SELECT c.Plate, MAX(c.Recordedat) AS Recordedat
FROM c
WHERE c.Plate IN ('plate1', 'plate2', 'plate3') 
GROUP BY c.Plate