You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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?
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
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:
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?
Beta Was this translation helpful? Give feedback.
All reactions