CODEWITHSUNDEEP
mysql
chink
chink

Reputation: 1643

SQL query to get latest data from multiple columns when there are nulls in the data

I have a SQL table which looks like

+--------------------------+------------+-----------------+---------+------+-------+-------------+-------------------------+----------------------------+-------+-------+
| 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 |        NULL |                    NULL |                      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 |        NULL |                    NULL |                      27.54 | NULL  | ABCD  |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:49:01.529593 |     823 |   91 |     7 |        45   |                     72  |                       NULL | NULL  | ABCD  |

I want to write a query where I get the latest non null values(based on timestamp) of nvo_airflow,nvo_air_damper_position,nvo_temperature_sensor_pps for all the vavIds or say for a particular vavId 7 .

If we consider for vav 7 it should return

nvo_airflow,nvo_air_damper_position,nvo_temperature_sensor_pps, vavId
45,72,26.49

I could write a query for getting latest values if there are no nulls

SELECT 
t1.deviceId
,t1.date
,t1.vavId
,t1.timestamp
,t1.nvo_airflow
,t1.nvo_air_damper_position
,t1.nvo_temperature_sensor_pps
FROM
(SELECT 
deviceId
,date
,nvo_airflow
,nvo_air_damper_position
,nvo_temperature_sensor_pps
,vavId
,timestamp
,counter
FROM 
vavData 
WHERE 
date=%s 
and floor=%s) t1 INNER JOIN
 (SELECT 
date
,max(timestamp) as timestamp
,vavId 
FROM 
vavData WHERE 
date=%s AND 
floor=%s 
GROUP BY vavId) t2
ON t1.timestamp = t2.timestamp 
ORDER BY t1.vavId

but I am confused how to do get latest data for multiple columns where there are some nulls. How can I write a query for this?

Upvotes: 1

Views: 62

Answers (1)

Luuk
Luuk

Reputation: 14939

SELECT
    nvo_airflow,nvo_air_damper_position,nvo_temperature_sensor_pps, vavId
FROM
    vavData 
WHERE NOT ( nvo_airflow IS NULL 
            OR nvo_air_damper_position IS NULL 
            OR nvo_temperature_sensor_pps IS NULL )
ORDER BY `date` DESC, `timestamp` DESC

select, and order, the data where not will exclude the columns having a NULL-value.

This query will return all rows, the latest first.

Upvotes: 2

Related Questions