add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
+--------------------------+------------+-----------------+---------+------+-------+-------------+-------------------------+----------------------------+-------+-------+
| deviceID | date | timestamp | counter | rssi | vavId | nvo_airflow | nvo_air_damper_position | nvo_temperature_sensor_pps | block | floor |
+--------------------------+------------+-----------------+---------+------+-------+-------------+-------------------------+----------------------------+-------+-------+
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:40:01.513066 | 805 | 91 | 7 | NULL | NULL | 26.49 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:41:01.542272 | 807 | 94 | 5 | 50 | 64 | 26.37 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:43:01.699023 | 811 | 90 | 7 | 50 | NULL | NULL | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:46:01.412259 | 817 | 64 | 26 | NULL | NULL | 25.85 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:48:01.576133 | 821 | 91 | 26 | 55 | 42 | NULL | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:49:01.529593 | 823 | 91 | 7 | 45 | 72 | NULL | NULL | ABCD |
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+--------------------------+------------+-----------------+---------+------+----' at line 1
create table vavDataOptimized(
deviceID varchar(30)
,date1 date
,timestamp1 time
,vavId int
,nvo_airflow int
,nvo_air_damper_position int
,nvo_temperature_sensor_pps int
);
insert into vavDataOptimized values('fd00::212:4b00:1957:d616','2020-02-29','12:40:01.513066',7,NULL,NULL,26.49)
insert into vavDataOptimized values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:41:01.542272',5,50,64,26.37)

insert into vavDataOptimized values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:43:01.699023',7,50,NULL,NULL)

insert into vavDataOptimized values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:46:01.412259',26,NULL,NULL,25.85)

insert into vavDataOptimized values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:48:01.576133',26,55,42,NULL)

insert into vavDataOptimized values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:49:01.529593',7,45,72, NULL )

select vavId,nvo_airflow,nvo_air_damper_position,nvo_temperature_sensor_pps
from
(select vavId, @rownum1 := @rownum1 + 1 as rownum1 from
(select vavId
from vavDataOptimized
where vavId is not NULL
ORDER BY date1,timestamp1 DESC LIMIT 3) a
CROSS JOIN (SELECT @rownum1 := 0) v) a,
(select nvo_airflow, @rownum2 := @rownum2 + 1 as rownum2 from
(select nvo_airflow
from vavDataOptimized
where nvo_airflow is not NULL
ORDER BY date1,timestamp1 DESC LIMIT 3) b
CROSS JOIN (SELECT @rownum2 := 0) v) b,
(select nvo_air_damper_position, @rownum3 := @rownum3 + 1 as rownum3 from
(select nvo_air_damper_position
from vavDataOptimized
where nvo_air_damper_position is not NULL
ORDER BY date1,timestamp1 DESC LIMIT 3) c
CROSS JOIN (SELECT @rownum3 := 0) v) c,
(select nvo_temperature_sensor_pps, @rownum4 := @rownum4 + 1 as rownum4 from
(select nvo_temperature_sensor_pps
from vavDataOptimized
where nvo_temperature_sensor_pps is not NULL
ORDER BY date1,timestamp1 DESC LIMIT 3) d
CROSS JOIN (SELECT @rownum4 := 0) v) d
where rownum1 = rownum2
and rownum1 = rownum3
and rownum1 = rownum4
vavId nvo_airflow nvo_air_damper_position nvo_temperature_sensor_pps
7 45 72 26
26 55 42 26
26 50 64 26