[sqlite] Select values from a time series spaced at least a mininum distance apart

[E.Pasma]

20 nov 2015, 09:19, Clemens Ladisch:

> E.Pasma wrote:
>> An aggregate function can still be used in a sub-query for a column  
>> value.  For the example with integers:
>>
>> WITH RECURSIVE
>> breaks(t) AS (
>>   SELECT 1
>>   UNION
>>   SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10)
>>   FROM   breaks
>> )
>> SELECT t FROM breaks
>> ;
>
> In SQLite, a scalar subquery returns NULL when nothing is found; this
> needs to be filtered out in the outermost query:
>
> WITH RECURSIVE breaks(t) AS (
>  SELECT min(x) FROM test
>  UNION
>  SELECT (SELECT min(x)
>          FROM test
>          WHERE x > t + 2)
>  FROM breaks
> )
> SELECT t
> FROM breaks
> WHERE t IS NOT NULL;


OK. We can also filter the NULL straight away within the CTE:

WITH RECURSIVE
  breaks(t) AS (
    SELECT 1
    UNION
    SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10) AS t2
    FROM   breaks
    WHERE  t2 IS NOT NULL
  )
SELECT t FROM breaks