I have a table of data in the following format. There are devices, each outputting timeseries data with a varying number of sensors. Each message is timestamped with an associated value for the sensor at a particular time.
+--------+--------+---------------------+--------------+
| device | sensor | time_stamp | sensor_value |
+--------+--------+---------------------+--------------+
| 1 | 1 | 2020-01-12 12:00:00 | 10 |
| 1 | 1 | 2020-01-12 13:00:00 | 100 |
| 1 | 2 | 2020-01-12 12:00:00 | 10 |
| 1 | 2 | 2020-01-12 13:00:00 | 100 |
| 1 | 3 | 2020-01-12 12:00:00 | 10 |
| 1 | 3 | 2020-01-12 13:00:00 | 100 |
| 2 | 1 | 2020-01-12 12:00:00 | 10 |
| 2 | 1 | 2020-01-12 13:00:00 | 100 |
| 2 | 2 | 2020-01-12 12:00:00 | 10 |
| 2 | 2 | 2020-01-12 13:00:00 | 100 |
| 2 | 3 | 2020-01-12 12:00:00 | 10 |
| 2 | 3 | 2020-01-12 13:00:00 | 100 |
+--------+--------+---------------------+--------------+
I am trying to find the first and last (based on timestamp) values for each sensor, grouped by devices. In other words, what was the first and last value for each sensor for each device? Output should be:
first:
+--------+--------+---------------------+---------+
| device | sensor | first_t | first_v |
+--------+--------+---------------------+---------+
| 1 | 1 | 2020-01-12 12:00:00 | 10 |
| 1 | 2 | 2020-01-12 12:00:00 | 10 |
| 1 | 3 | 2020-01-12 12:00:00 | 10 |
| 2 | 1 | 2020-01-12 12:00:00 | 10 |
| 2 | 2 | 2020-01-12 12:00:00 | 10 |
| 2 | 3 | 2020-01-12 12:00:00 | 10 |
+--------+--------+---------------------+---------+
last:
+--------+--------+---------------------+--------+
| device | sensor | last_t | last_v |
+--------+--------+---------------------+--------+
| 1 | 1 | 2020-01-12 13:00:00 | 100 |
| 1 | 2 | 2020-01-12 13:00:00 | 100 |
| 1 | 3 | 2020-01-12 13:00:00 | 100 |
| 2 | 1 | 2020-01-12 13:00:00 | 100 |
| 2 | 2 | 2020-01-12 13:00:00 | 100 |
| 2 | 3 | 2020-01-12 13:00:00 | 100 |
+--------+--------+---------------------+--------+
When I use the following query:
SELECT
device, sensor,
MIN(time_stamp) AS first_t,
sensor_value AS first_v
FROM
example_table
GROUP BY device , sensor
ORDER BY device
I get the correct first sensor_values. The problem comes when trying to select the last (MAX) values. I get the correct time stamp, however the associated sensor value is still the earliest. I know this is a feature with max and the solution is something to do with a WHERE query, or order ASC with LIMIT 1, however I cannot seem to find the right query for an efficient query (>250 million database rows)
