CTE question...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

CTE question...

Brian Curley
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CTE question...

petern
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CTE question...

Keith Medcalf
In reply to this post by Brian Curley

Your result data does not appear in the input data.  How do you explain that?

Are you trying to perform a query and simply add a fictitious "logical row number" (ie, a result row number) for some reason to the results of a the rather simple query:

SELECT DISTINCT code_key FROM _misc_log ORDER BY code_key DESC;

Why cannot your application merely compute this for itself (ie, the first step returns row 1, the next row 2, and so on and so forth).  Why do you want to add a logical row number to the results?

Relational Databases are based on Relational Algebra.  There is no such thing as a "Positional Row Number" in a result set -- nor can I think of any reason that you would want one.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Brian Curley
>Sent: Friday, 15 September, 2017 16:55
>To: SQLite mailing list
>Subject: [sqlite] CTE question...
>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CTE question...

Clemens Ladisch
In reply to this post by Brian Curley
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CTE question...

Brian Curley
that is perfect.

Thanks, Clemens!

Regards.

Brian P Curley

On Sat, Sep 16, 2017 at 5:14 AM, Clemens Ladisch <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users