Re: Continuous NULL value in a column

[Benoit St-Jean]

This should get you started... Adjust accordingly for your needs! Hope this helps!

If you want to understand how it works, I left all columns in the joined table t1 so you can see what is going on.

CAVEAT: this will only work if *there is no gap* in the sequence column!

SET @nullcount=0;
SELECT t1.id, t1.consecutivenulls
FROM
(
SELECT s1.id, s1.sequence, s1.value, s2.sequence AS seq2, s2.value AS value2,
(@nullcount := IF(s1.value IS NULL, @nullcount + 1, 0)) AS consecutivenulls
FROM equipment s1
LEFT JOIN equipment s2 ON (s1.id=s2.id AND s1.sequence = s2.sequence-1)
ORDER BY s1.id, s1.sequence
) t1
WHERE (value2 IS NOT NULL AND value IS NULL) OR seq2 IS NULL;