{"id":1471,"date":"2019-02-23T12:00:35","date_gmt":"2019-02-23T04:00:35","guid":{"rendered":"https:\/\/www.techcoil.com\/blog\/?p=1471"},"modified":"2020-05-12T10:26:13","modified_gmt":"2020-05-12T02:26:13","slug":"sql-statement-for-selecting-the-latest-record-in-each-group","status":"publish","type":"post","link":"https:\/\/www.techcoil.com\/blog\/sql-statement-for-selecting-the-latest-record-in-each-group\/","title":{"rendered":"SQL statement for selecting the latest record in each group"},"content":{"rendered":"

When you have multiple IoT<\/a> devices of the same type, you may collect data in a single database table.<\/p>\n

In such a case, you may choose to group data based on the device ID.<\/p>\n

So how do you display the latest data capture from each device?<\/p>\n

In this situation, we can choose to run an SQL statement that will return the latest record in each group.<\/p>\n

A sample scenario that requires an SQL statement for selecting the latest record in each group<\/h2>\n

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

When the endpoint receives the data, it saves the data into an SQL database with the following schema:
\nDEVICE_DATA(ID, DEVICE_ID, HUMIDITY, TEMPERATURE, TIMESTAMP_UNIX_EPOCH)<\/code><\/p>\n

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.<\/p>\n

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<\/strong>.<\/p>\n

Constructing the SQL statement for selecting the latest record in each group<\/h2>\n

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:<\/p>\n

\r\nSELECT DEVICE_ID, max(TIMESTAMP_UNIX_EPOCH) as MaxTime\r\nFROM DEVICE_DATA\r\nGROUP BY DEVICE_ID\r\n<\/pre>\n
After the above statement is executed, we get a table of unique device id alongside the maximum timestamp in the group.<\/p>\n
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:<\/p>\n
\r\nSELECT dd.DEVICE_ID, dd.HUMIDITY, dd.TEMPERATURE, dd.TIMESTAMP_UNIX_EPOCH\r\nFROM \r\n(SELECT DEVICE_ID, max(TIMESTAMP_UNIX_EPOCH) as MaxTime \r\nFROM DEVICE_DATA \r\nGROUP BY DEVICE_ID) r\r\nINNER JOIN DEVICE_DATA dd\r\nON dd.DEVICE_ID = r.DEVICE_ID AND dd.TIMESTAMP_UNIX_EPOCH = r.MaxTime\r\n<\/pre>\n
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.<\/p>\n\n