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

[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;


Regards,
Clemens