Get top 1 row of each Id by Where in

Question

I have a table which I want to get the latest entry for each parameters in where in

select GpsData.VehicleId, GpsData.Id, GpsData.DateTime
from GpsData
where GpsData.VehicleId in (44, 1054, 1055, 31, 22, 1058)
order by GpsData.VehicleId desc;

Answer 1

You can use TOP 1 WITH TIES

SELECT TOP 1 WITH TIES
    VehicleId, 
    Id, 
    [DateTime]
FROM GpsData
WHERE VehicleId in (44, 1054, 1055, 31, 22, 1058)
ORDER BY ROW_NUMBER() OVER (PARTITION BY VehicleId ORDER BY [DateTime] DESC)

We can use dynamic query for that as below:

 DECLARE @Ids NVARCHAR(MAX) = '44, 1054, 1055, 31, 22, 1058', @sql NVARCHAR(3000)

 SET @sql = '
    SELECT TOP 1 WITH TIES VehicleId, 
        Id, 
        [DateTime] 
    FROM GpsData 
    WHERE VehicleId IN (' + @ids + ') 
    ORDER BY ROW_NUMBER() OVER (PARTITION BY VehicleId ORDER BY [DateTime] DESC)'

EXEC(@sql)