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
