Retrieving Latest device_location Records for Each Vehicle Using Group By in SurrealDB

[MrJSdev]

I have a table named device_locations that stores multiple locations per vehicle_id. My goal is to fetch the most recent location record for each vehicle, including all associated column data. However, when I attempt to use a GROUP BY clause, it results in an array that I'm unable to utilize for further comparisons and extracting the desired records.

Here's the base query I started with:

SELECT vehicle_uuid, time::max(created_at) AS latest_created_at FROM device_locations GROUP BY vehicle_uuid;

SELECT * from device_locations, (SELECT vehicle_uuid, time::max(created_at) AS latest_created_at FROM device_locations GROUP BY vehicle_uuid) as where vehicle_uuid = sub_query.vehicle_uuid AND created_at = sub_query.latest_created_at;

I'm aiming to retrieve the latest record for each vehicle from the device_locations table. However, I'm facing challenges in managing this with SurrealDB, as the grouped results aren't usable as I expected.

Could you help refine this approach or suggest an alternative method to achieve the desired outcome in SurrealDB?