I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to offset to find the first not null value?
My line of code is below everything is working except I cannot get the offset set.
SELECT * FROM (SELECT @row := @row+1 AS rownum, ttime,temperature,humidity,outsidetemp
FROM (SELECT @row :=0) r, tempHumiditydata) ranked
WHERE rownum % 5 = 1
ORDER BY rownum DESC LIMIT 2020 ??????OFFSET (WHERE outsidetemp IS NOT NULL)????????
Additional background. The outsidetemp is being logged once for every 60 of the other data points the values in between these points is Null.
My code above is being used to graph the data so it is important I start at the first not null value to capture the remaining not null values (starting at the wrong point and I could not get any data for outsidetemp).
I am skipping every 5th row to reduce the amount of data in a wider time frame graph.
Here is an example of what I need
Ttime temp humidity outsidetemp temp humidity outsidetemp
8:00 71 71 50 NULL
7:00 72 49 80
6:00 73 48 NULL
5:00 73 48 NULL
4:00 73 48 NULL
3:00 69 51 76
Using only every other record starting at the first non null outsidetemp yields
7:00 72 49 80
5:00 73 48 NULL
3:00 69 51 76
I can then use the above to graph temp, humidity and outside temp versus time. I just interpolate between non null outside temp values.
