Using ArcGIS Desktop 10.5.1 I have a point featureclass in an Microsoft SQL Server 2012 Enterprise Geodatabase (ArcGIS Server 10.5) that holds MDS (mobility ride share scooter) data. The feature class has two fields we are interested in, [vehicle_id] and [route_timestamp]. The [vehicle_id] field contains a unique ID for each scooter and [the route_timestamp] contains the timestamp of the scooter point.
Each [vehicle_id] contains multiple timestamps (making a breadcrumb trail the scooter traveled)
I only want to know the scooters last location (not interested in any of the previous breadcrumbs) To only see the latest timestamp I figured I would use a TOP 1 SQL query and group them by [vehicle_id].
My definition query on the layer is:
route_timestamp IN ( SELECT MAX ( route_timestamp ) FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs GROUP BY [vehicle_id])
This query takes the records from around 20,000 records to only 318, but it is returning multiple timestamps for a single vehicle_id, when in fact it should only be returning the top 1.
I have tested a similar query on a different dataset and it works, so I am wondering what is going on here, and why I am getting the results I am getting.
With further testing I got this query to work and return the wanted results directly in SQL but not in a layer definition query.
SELECT MAX (route_timestamp),vehicle_id FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs where vehicle_id in (SELECT distinct (vehicle_id) FROM ENG_GDB.ENGGISEDITOR.MDS_trips_breadcrumbs) group by vehicle_id
Still trying to figure out how to put a definition query directly on the layer.