Ahh, thank you very much for not only solving my problem, but also showing me some features of the language I wasn't aware of. Greatly appreciated!
Best regards, Ilja On 20/11/15 14:15, E.Pasma wrote: > 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
