Advanced MySQL query to get range of latest records with same id and different subids

Question

I have a table called sensorvalue like this:

station_id   sensor_id   Timestamp            Value
----------------------------------------------------------
1            1           2013-09-04 12:00:00  12.2
1            2           2013-09-04 12:00:00  13.1
1            3           2013-09-04 12:00:00  13.2
1            1           2013-09-04 12:05:00  12.1
1            2           2013-09-04 12:05:00  14.1
1            3           2013-09-04 12:05:00  13.2
1            1           2013-09-04 12:10:00  12.5
1            2           2013-09-04 12:10:00  13.3
1            3           2013-09-04 12:10:00  14.1

I need a MySQL query that gets all the latest values of all three sensors of station with station_id 1. I wonder if this is possible with one MySQL statement.

I've managed to get the data out of the table, but not in an efficient way. Now I first SELECT all different sensors for station 1 and afterthat I call another MySQL SELECT statement foreach sensor. Some of the stations have over 10 sensors, so with this approach I need at least 11 MySQL statements to get the data out of the database.

I would like to include this in one MySQL statement. Is that possible? And how?

have you tried writing query that makes use of group by clause? something like group by sensor_id, order by Timestamp desc ? — Maximus2012, Sep 04 '13 at 15:25
Consider providing a sqlfiddle TOGETHER WITH the desired result. — Strawberry, Sep 04 '13 at 15:26
possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Marc B, Sep 04 '13 at 15:28
The answer is _yes_, it's possible... a GROUP_CONCAT and ORDER BY Timestamp DESC and LIMIT combination isn't even that advanced, IMHO — Elias Van Ootegem, Sep 04 '13 at 15:28

score 2 · Answer 1 · answered Sep 04 '13 at 15:27

2

There are multiple ways to write this. The follow is probably the simplest code, using a substring_index()/group_concat() trick:

select sensor_id, max(TimeStamp),
       substring_index(group_concat(Value) order by TimeStamp desc), ',', 1) as LastValue
from sensorvalue sv
where station_id = 1
group by sensor_id;

EDIT:

All the timestamp values look like they are the same. If this is true, you can also do:

select sv.*
from sensorvalue sv
where station_id = 1 and
      timestamp = (select max(timestamp) from sensorvalue where station_id = 1);

answered Sep 04 '13 at 15:27

Gordon Linoff

1,242,037
58
646
786

Not all timestamp values are always equal. The update frequency of the sensors is not equal, so the second option will not work. The first option is a briliant solution. A very short MySQL statement and an EXPLAIN of the query shows that it only executes one simple query. However I doubt whether this is a smart solution with many records. A ran the query in a test database with about 100 records per sensor. In the production database every sensor will write a record every 5 minutes. group_concat(Value order by TimeStamp desc) will return a very large string with all the values of all records. – Luc Sep 05 '13 at 07:04
Update of my comment: just found in the documentation that the result of a GROUP_CONCAT() has a maxlength (default 1024). So I don't think there will be an issue with a huge number of records. From the documentation: "The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024" – Luc Sep 05 '13 at 07:18

gwc · Accepted Answer · 2013-09-04T19:02:06.673

0

Retrieve the latest reading for all sensors on all stations.

SELECT s.*
  FROM (SELECT station_id, sensor_id, MAX(Timestamp) as max_timestamp
          FROM sensorvalue
         GROUP BY station_id, sensor_id
       ) m
  JOIN sensorvalue s
    ON s.station_id = m.station_id
   AND s.sensor_id  = m.sensor_id
   AND s.Timestamp  = m.max_timestamp

To limit it to a specific station.

SELECT s.*
  FROM (SELECT station_id, sensor_id, MAX(Timestamp) as max_timestamp
          FROM sensorvalue
         WHERE station_id = 1
         GROUP BY station_id, sensor_id
       ) m
  JOIN sensorvalue s
    ON s.station_id = m.station_id
   AND s.sensor_id  = m.sensor_id
   AND s.Timestamp  = m.max_timestamp

edited Sep 04 '13 at 19:02

answered Sep 04 '13 at 15:33

gwc

1,273
7
12

Just to let you knoww: Although this solution worked fine, I decided not to use this query anymore. The reason is performance in a big database. I have now rewritten this and use php plus serveral MySQL statements. – Luc Jun 23 '14 at 13:30
I have a similar setup, and I've found that with a few million rows in the database this query is slow. So I've replaced every occurrence of `millionrowtable` with `(select * from millionrowtable order by timestamp desc limit 1000)` and it goes 10-20x faster. YMMV. – Eric Nelson Dec 31 '14 at 12:04

Advanced MySQL query to get range of latest records with same id and different subids

2 Answers2