I have a database that stores IoT devices data. There are hundreds of different devices, all use the same table. Some devices are refrigerators, air conditioner, Microwaves, light sensors, etc. These devices send data each minute or so, each are identified by a unique ID.
I need to query the database for hundreds of devices so that I get only the last row of data each device has generated.
IS there any way to query the database so the result is a list of all the specified devices including the last row of data each one generated? Results must be DISTINCT
For example:
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id = 1 ORDER BY sd.TimeStamp DESC
The query above will retrieve the last data row that the device generated, for one device it works like a charm.
The problem is that I need the last row of data from 300 devices. I could run the same query 300 times, but that is not viable.
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id = 1 ORDER BY sd.TimeStamp DESC
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id = 2 ORDER BY sd.TimeStamp DESC
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id = 3 ORDER BY sd.TimeStamp DESC
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id = 4 ORDER BY sd.TimeStamp DESC
...
I tried the following but not successfully:
SELECT TOP(1) * FROM SmartDevice as sd where sd.Id in (1,2,3,4, ... 300) ORDER BY sd.TimeStamp DESC
SELECT DISTINCT(Id) * FROM SmartDevice as sd where sd.Id in (1,2,3,4, ... 300) ORDER BY sd.TimeStamp DESC GROUP BY Id
I also tried with SQL Stored Procedures but it is not possible to use an array of parameters. As such I would have to repeat the SP n times.
