CODEWITHSUNDEEP
mysqlsqldategroup-by
immanis
immanis

Reputation: 70

Select latest row per group having date less than specified date

This is a bit of a tricky one, but I have a table that looks like:

Time                  Meter   Value
2018-09-15 11:00:00   IU       0.105
2018-09-15 11:00:00   GD       1.648
2018-09-10 10:00:00   OU      -0.018
2018-09-10 10:00:00   GD       1.659
2018-09-01 19:00:00   OU       0.003
2018-09-01 19:00:00   OD       2.188
2018-09-01 19:00:00   IU       0.096
2018-09-01 19:00:00   GD       1.670

For any given time, I want to get the closest previous value for each distinct meter: e.g: for time "2018-09-14 12:00:00" I want to get:

2018-09-10 10:00:00 OU  -0.018
2018-09-10 10:00:00 GD  1.659
2018-09-10 04:15:00 KD  0.737
2018-09-01 19:00:00 IU  0.096

I initially thought that this query might work: 

SELECT `meter`, max(`time`) time, `value` 
FROM `data` 
WHERE `time`<= '2018-09-15 12:00:00' 
GROUP BY `meter`;

It gave the expected times, but different values.

I then thought that the best way of doing it would be to use this query as a basis and then run another query using the output:

SELECT `meter`, max(`time`) time 
FROM `data` 
WHERE `time`<= '2018-09-15 12:00:00' 
GROUP BY `meter`;

Output:

2018-09-10 10:00:00 OU
2018-09-10 10:00:00 GD
2018-09-10 04:15:00 KD
2018-09-01 19:00:00 IU

Then on each of the rows, run: 

SELECT `value` 
FROM `data` 
where `time` = '...' AND `meter` - '...';

The question is then, how can I roll it into one query such that a third columns is added which contains the result of the second query, for each of the rows?

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I think the simplest method is a correlated subquery:

SELECT d.meter, max(d.time) as time,
       (SELECT d2.value
        FROM data d2
        WHERE d2.meter = d.meter AND
              d2.time <= '2018-09-14 12:00:00'
        ORDER BY d2.time DESC
        LIMIT 1
       ) AS value
FROM data d
WHERE d.time <= '2018-09-14 12:00:00'
GROUP BY d.meter;

Unless, of course, you are using MySQL 8.0. In that case, lag() is the best solution:

select d.*, lag(value) over (partition by meter order by time) as prev_value
from data d;

Upvotes: 3

Related Questions