Try running your subquery by itself and see if the first row's code_key is
what you want for every column of your recursive outer query:
SELECT DISTINCT
code_key
FROM
_misc_log
WHERE
code_key
!= (SELECT
max(code_key)
FROM
_misc_log )
ORDER BY 1 desc
On Fri, Sep 15, 2017 at 3:54 PM, Brian Curley <[email protected]> wrote:
> Admittedly it's a bit of a hack and there may be alternate approaches to
> some of it, but I am curious if there's an issue within the WITH behavior.
> It could just my approach.
>
> DDL for my base table:
>
> CREATE TABLE _misc_log (
>
> CODE_KEY NOT NULL,
>
> CODE_VAL NOT NULL,
>
> ATTRIB,
>
> PRIMARY KEY (
>
> CODE_KEY,
>
> CODE_VAL
>
> )
>
> );
>
>
> This _misc_log is an ad hoc logging table, where I throw a time-stamp,
> table name, and a record count, respectively. I am just storing strings in
> the code_* fields, so it can really be anything. The purpose is produce a
> quick listing of the unique values in the code_key field, alongside of a
> proper row number. (The native result of rowid from the table is not
> useful, since they are typically hundreds of rows apart.)
>
> Sample values:
>
> code_key
> 20170914-1200
> 20170914-1822
> 20170915-0855
> 20170915-1718
>
>
> I can get the recursion to work properly to give me the row sequence I
> need, but it spins a little wide on sub-query content. Rather than give me
> a nice listing of the recursive row number and the code_key string, it
> works only for the first two lines...and then spirals into what appears to
> be a Cartesian.
>
> I'd expected:
>
> my_row code_key
>
> 1 20170915-1718
> 2 20170915-0855
> 3 20170915-1822
> 4 20170915-1200
> 5 ...
>
>
> I got:
>
> my_row code_key
>
> 1 20170915-1718
> 2 20170915-0855
> 3 20170915-0855
> 4 20170915-0855
> 5 20170915-0855
>
>
> CTE used:
>
> -- WITH cte -- Name your "logical" table.
> -- AS (SELECT 1 AS n -- Initialize your counter
> value, plus any other columns...
> -- UNION ALL -- (UNION rules...)
> -- SELECT n + 1 -- ...then recursively loop
> through the rest of the logical table...
> -- FROM cte -- ...using subqueries to
> provide for content columns
> -- WHERE n < 50 -- ...up to the point of the
> max of n, or any other conditionals.
> -- ) --
> -- SELECT n -- Then SELECT...
> -- FROM cte -- ...from the logical table.
> -- [ LIMIT x ] -- [Alternatively, or
> additionally, limit output as needed.]
> -- ; --
>
> --CREATE VIEW vw_my_row_logid AS
> WITH cte_name --(my_row, code_key)
> AS (
> SELECT -- Base record
> 1 my_row
> ,(SELECT
> max(code_key)
> FROM
> _misc_log
> ) log_id
> 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
> -- WHERE my_row <= 5
> )
> -- Resultset here...
> SELECT *
> FROM cte_name
> LIMIT 5
> ;
> ===
>
>
> Any thoughts on this?
>
> (The internal WHERE and the external LIMIT are redundant.)
>
>
> Regards.
>
> Brian P Curley
> _______________________________________________
> 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
