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- >[email protected]] 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 >[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
