SQL statement for selecting the latest record in each group

When you have multiple IoT devices of the same type, you may collect data in a single database table.

In such a case, you may choose to group data based on the device ID.

So how do you display the latest data capture from each device?

In this situation, we can choose to run an SQL statement that will return the latest record in each group.

A sample scenario that requires an SQL statement for selecting the latest record in each group

For the purpose of this post, assume that we have deployed multiple Raspberry Pis to capture temperature and humidity in different locations. After a Raspberry Pi reads temperature and humidity from a DHT11 sensor, it sends them to a HTTP server endpoint.

When the endpoint receives the data, it saves the data into an SQL database with the following schema:
DEVICE_DATA(ID, DEVICE_ID, HUMIDITY, TEMPERATURE, TIMESTAMP_UNIX_EPOCH)

In addition to capturing data, there is also another endpoint returns the most recent temperature and humidity reading captured by each of the Raspberry Pi.

Given that, we choose to run an SQL statement that will return the latest record in each group; with the device ID serving as the group number.

Constructing the SQL statement for selecting the latest record in each group

In order to get the SQL statement for selecting the latest record in each group, we first need a query to get the latest timestamp:

SELECT DEVICE_ID, max(TIMESTAMP_UNIX_EPOCH) as MaxTime
FROM DEVICE_DATA
GROUP BY DEVICE_ID

After the above statement is executed, we get a table of unique device id alongside the maximum timestamp in the group.

However, this statement does not give the temperature and humidity readings that we will need for our endpoint. Therefore, we need to use a inner join statement to join the results with the original device table:

SELECT dd.DEVICE_ID, dd.HUMIDITY, dd.TEMPERATURE, dd.TIMESTAMP_UNIX_EPOCH
FROM 
(SELECT DEVICE_ID, max(TIMESTAMP_UNIX_EPOCH) as MaxTime 
FROM DEVICE_DATA 
GROUP BY DEVICE_ID) r
INNER JOIN DEVICE_DATA dd
ON dd.DEVICE_ID = r.DEVICE_ID AND dd.TIMESTAMP_UNIX_EPOCH = r.MaxTime

By joining the result from the previous SQL statement, we will be able to augment the results with the most recent temperature and humidity reading that each Raspberry Pi had captured.

About Clivant

Clivant a.k.a Chai Heng enjoys composing software and building systems to serve people. He owns techcoil.com and hopes that whatever he had written and built so far had benefited people. All views expressed belongs to him and are not representative of the company that he works/worked for.