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
