CODEWITHSUNDEEP
mysqlsql
Lukas
Lukas

Reputation: 510

Inner join most recent row

I've got 2 tables I need to join for a select statement which should give me only the last row for each sensorID (the most recent value of every sensorID). Here are my tables:

sensorID| Name | Description |   stationID
---------------------------------
1 |  Name1   |  Desc1   | 2
2 |  Name2   |  Desc2   | 1
3 |  Name3   |  Desc3   | 3
4 |  Name4   |  Desc4   | 2

Values

vID | sensorID | Date | Time |  value
---------------------------------
1 |  1   |  2016-12-29   | 15:08:00 | 0.2
2 |  1   |  2016-12-29   | 15:10:00 | 0.21
3 |  1   |  2016-12-29   | 15:12:00 | 0.4
4 |  4   |  2016-12-29   | 15:14:00 | 12.2

I tried running the following:

SELECT * FROM H INNER JOIN Values V ON H.sensorID = V.sensorID WHERE H.stationID = 2;

Which produced these results:

sensorID | Name | Description | stationID | vID | sensorID | Date   | Time     | value
1    | Name1|  Desc1      | 2         | 1   |    1     |2016-12-29  | 15:08:00 | 0.2
1    | Name1|  Desc1      | 2         | 2   |    1     |2016-12-29  | 15:10:00 | 0.21
1    | Name1|  Desc1      | 2         | 3   |    1     |2016-12-29  | 15:12:00 | 0.4
4    | Name4|  Desc4      | 2         | 4   |    1     |2016-12-29  | 15:14:00 | 12.2

But I am looking for this output:

sensorID | Name | Description | stationID | vID | sensorID | Date   | Time     | value
1    | Name1|  Desc1      | 2         | 3   |    1     |2016-12-29  | 15:12:00 | 0.4
4    | Name4|  Desc4      | 2         | 4   |    1     |2016-12-29  | 15:14:00 | 12.2

Upvotes: 0

Views: 3299

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want the last row for each sensor, I'd be inclined to use the id column rather than the date/time:

SELECT *
FROM H INNER JOIN
     Values V
     ON H.sensorID = V.sensorID
WHERE H.stationID = 2 AND
      V.id = (SELECT MAX(v2.id) FROM Values v2 WHERE v2.SensorId = v.SensorId);

This version can take good advantage of an index on Values(SensorId, id).

Upvotes: 4

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY this

select *
from table1 a
inner join table2 b on a.sensorID = b.sensorID
inner join (select sensorID, max(concat(dated, timed)) dateT from table2
                                              group by sensorID) t on t.sensorID = b.sensorID
    and t.dateT = concat(b.DateD, b.TimeD)

OUTPUT

sensorID    Name    DescR   stationID   vID sensorID    DateD       TimeD               value   sensorID    dateT
1           Name1   Desc1   2       3           1       2016-12-29  15:12:00.0000000    0.4     1           2016-12-2915:12:00.0000000
4           Name4   Desc4   2       4           4       2016-12-29  15:14:00.0000000    12.2    4           2016-12-2915:14:00.0000000

Upvotes: 1

Related Questions