that is perfect. Thanks, Clemens!
Regards. Brian P Curley On Sat, Sep 16, 2017 at 5:14 AM, Clemens Ladisch <[email protected]> wrote: > Brian Curley wrote: > > WITH cte_name --(my_row, code_key) > > AS ( > > SELECT -- Base record > > 1 my_row > > ,(SELECT > > max(code_key) > > FROM > > _misc_log > > ) log_id > > This does not need a subquery: > > SELECT 1, max(code_key) FROM _misc_log > > > UNION ALL > > SELECT -- Recursion records > > my_row + 1 > > ,(SELECT DISTINCT > > code_key > > FROM > > _misc_log > > WHERE > > code_key > > != (SELECT > > max(code_key) > > FROM > > _misc_log ) > > ORDER BY 1 desc > > ) > > FROM > > cte_name > > This does not refer to cte_name.code_key, so every step will return the > same value. And "key != max" works only for the first recursion step. > > The CTE needs to retrieve the largest key that is smaller than the > previous key: > > WITH cte_name(my_row, code_key) AS ( > SELECT 1, > max(code_key) > FROM _misc_log > > UNION ALL > > SELECT my_row + 1, > (SELECT max(code_key) > FROM _misc_log > WHERE code_key < cte_name.code_key) > FROM cte_name > WHERE EXISTS (SELECT * > FROM _misc_log > WHERE code_key < cte_name.code_key) > ) > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
