[sqlite] The Simplest SQLite Common Table Expression Tutorial

t-sqlite-common-table-expression-tutorial/ > > The Simplest SQLite Common Table Expression Tutorial > > I?ve been trying to wrap my head aroundCommon Table Expressions > <https://www.sqlite.org/lang_with.html> for a while, and all the tutorials > I?ve read started out with ?simp

[sqlite] The Simplest SQLite Common Table Expression Tutorial

Hello DB> Hey all, just wanted to share this in case anybody is also looking for DB> a very simple tutorial for CTE's in sqlite: DB> http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/ Yes, that was a great introduction to recursive SQL. Last wee

[sqlite] The Simplest SQLite Common Table Expression Tutorial

sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of David Barrett > Sent: Friday, September 25, 2015 4:17 PM > To: General Discussion of SQLite Database > Subject: [sqlite] The Simplest SQLite Common Table Expression Tutorial > > Hey all, just wanted to share this

[sqlite] The Simplest SQLite Common Table Expression Tutorial

Hey all, just wanted to share this in case anybody is also looking for a very simple tutorial for CTE's in sqlite: http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/ The Simplest SQLite Common Table Expression Tutorial I?ve been trying to wrap my head

Re: [sqlite] "Common Table Expression"

On Jan 25, 2014, at 6:25 PM, Petite Abeille wrote: > > On Jan 25, 2014, at 6:05 AM, Keith Medcalf wrote: > >> Read the docs. It explains how recursive CTEs are computed and how UNION >> ALL vs UNION work in CTEs. > > > Hmmm… perhaps… doing

Re: [sqlite] "Common Table Expression"

>remark 2: >- >I'm using Keith buildMSVC.cmd file to compile (that I'm using without >knowing what it does exactly) > >I have one more compile error than with 21th version. >"c1 : fatal error C1083: Impossible d'ouvrir le fichier source : >'CSVImport.c' " > >==> Maybe it's normal. Yes.

Re: [sqlite] "Common Table Expression"

On Jan 25, 2014, at 6:05 AM, Keith Medcalf wrote: > Read the docs. It explains how recursive CTEs are computed and how UNION ALL > vs UNION work in CTEs. Hmmm… perhaps… doing is believing… so will experiment once the next SQLite release is officially out.

Re: [sqlite] "Common Table Expression"

On Jan 25, 2014, at 2:37 AM, James K. Lowden wrote: > Funny, we find ourselves on the opposite side of the compexity question > this time. Ehehehe… yes… the irony is duly noted :) But, ok, then, let welcome our new VALUES overlord. May it have a long and prosperous

Re: [sqlite] "Common Table Expression"

On 25/01/14 18:41, James K. Lowden wrote: On Sat, 25 Jan 2014 11:56:32 +0200 Elefterios Stamatogiannakis wrote: Wouldn't it be better instead of creating a new concept "row constructor", to use the existing row constructors, also known as virtual tables? Perhaps. I didn't

Re: [sqlite] "Common Table Expression"

On Sat, 25 Jan 2014 11:56:32 +0200 Elefterios Stamatogiannakis wrote: > Wouldn't it be better instead of creating a new concept "row > constructor", to use the existing row constructors, also known as > virtual tables? Perhaps. I didn't make up the term; it exists in

Re: [sqlite] "Common Table Expression"

On 25/01/14 03:37, James K. Lowden wrote: On Fri, 24 Jan 2014 23:51:11 +0100 Petite Abeille wrote: It's exactly the same as "SELECT ?", but a little bit easier to write. (It behaves like with INSERT, but is now available in every place where a SELECT would be

Re: [sqlite] "Common Table Expression"

hi again, With the version sqlite-amalgamation-201401242258 of this night. I found my error so timings are : timing with medium sudoku example : '17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..' 2 nested with = 3.06 sec (3.32 sec previous beta of 21rst) 8%

Re: [sqlite] "Common Table Expression"

>>> (2) What about cycles? How does one deal with them? >> With cycles, you probably want to use UNION instead of UNION ALL to >> stop at duplicate records. >Hmmm... not quite sure how this would play out in practice... how would the >recursion known when to stop? >Say, given a circular

Re: [sqlite] "Common Table Expression"

On Fri, 24 Jan 2014 23:51:11 +0100 Petite Abeille wrote: > > It's exactly the same as "SELECT ?", but a little bit easier to > > write. (It behaves like with INSERT, but is now available in every > > place where a SELECT would be allowed.) > > Hmmm? seems rather

Re: [sqlite] "Common Table Expression"

On Jan 24, 2014, at 11:32 PM, Clemens Ladisch wrote: > It's exactly the same as "SELECT …", but a little bit easier to write. > (It behaves like with INSERT, but is now available in every place > where a SELECT would be allowed.) Hmmm… seems rather pointless to me. select

Re: [sqlite] "Common Table Expression"

Petite Abeille wrote: > On Jan 24, 2014, at 2:31 PM, Richard Hipp wrote: >> Please see http://www.sqlite.org/draft/lang_with.html for draft >> documentation of the new Common Table Expression implementation for SQLite >> 3.8.3. Comments, criticism, and typo-corrections are

Re: [sqlite] "Common Table Expression"

On Jan 24, 2014, at 2:31 PM, Richard Hipp wrote: > Please see http://www.sqlite.org/draft/lang_with.html for draft > documentation of the new Common Table Expression implementation for SQLite > 3.8.3. Comments, criticism, and typo-corrections are appreciated. (1) What is this

Re: [sqlite] "Common Table Expression"

Op 24 jan 2014, om 17:31 heeft E.Pasma het volgende geschreven: Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven: Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments,

Re: [sqlite] "Common Table Expression"

Hi again, On my 3 level of with example, my systems seems to "hang" if : - I put a "distinct" keyword in goods definition, - or if I replace "union all" per a simple "union" . ** failure mode 1 (no union all in the neighbors) ** with digits(z, lp) AS ( select '1' as z, 1 as lp UNION ALL

Re: [sqlite] "Common Table Expression"

On 01/25/2014 01:00 AM, big stone wrote: AND NOT EXISTS ( SELECT 1 FROM ok AS lp WHERE ind=lp.c and z.z = substr(s, n, 1) ) s/ok/goods/ and s/lp.c/lp.r/ Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] "Common Table Expression"

hi again, I found my error so timings are : timing with medium sudoku example : '17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..' 2 nested with = 3.32 sec 1 nested with = 1.7 sec 3 nested with = 2.65 sec (1 nested with which could be 3 nested with) = 1.09 sec

Re: [sqlite] "Common Table Expression"

Hi, The speedest version of the sudoku, staying in the limit of lisibility would include 3 nested "with", timing with medium sudoku example : '17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..' 2 nested with = 3.32 sec 1 nested with = 1.7sec (1 nested with which

Re: [sqlite] "Common Table Expression"

Op 24 jan 2014, om 14:31 heeft Richard Hipp het volgende geschreven: Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments, criticism, and typo-corrections are appreciated. -- D. Richard

Re: [sqlite] "Common Table Expression"

On Fri, Jan 24, 2014 at 10:09 AM, Clemens Ladisch wrote: > Richard Hipp wrote: > > Please see http://www.sqlite.org/draft/lang_with.html for draft > > documentation of the new Common Table Expression implementation for > SQLite > > 3.8.3. Comments, criticism, and

Re: [sqlite] "Common Table Expression"

Richard Hipp wrote: > Please see http://www.sqlite.org/draft/lang_with.html for draft > documentation of the new Common Table Expression implementation for SQLite > 3.8.3. Comments, criticism, and typo-corrections are appreciated. Duplicated "the": * If a UNION operator connects the the

Re: [sqlite] "Common Table Expression"

Please see http://www.sqlite.org/draft/lang_with.html for draft documentation of the new Common Table Expression implementation for SQLite 3.8.3. Comments, criticism, and typo-corrections are appreciated. -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] "Common Table Expression"

On 1/17/2014, 8:24 AM, Jan Nijtmans wrote: Not necessary. I noticed that CTE was just merged to SQLite's trunk, so it apparently will be part of SQLite 2.8.3. Ahh great, I look forward to seeing that released in February (regular schedule) or whenever. The greater maintainability of code due

Re: [sqlite] "Common Table Expression"

On Fri, Jan 17, 2014 at 2:05 PM, Richard Hipp wrote: > On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille > wrote: >> On Jan 17, 2014, at 7:47 PM, big stone wrote: >> > - I just did my first recursive CTE under Ipython notebook. >>

Re: [sqlite] "Common Table Expression"

On Fri, Jan 17, 2014 at 2:24 PM, Petite Abeille wrote: > > On Jan 17, 2014, at 7:47 PM, big stone wrote: > > > - I just did my first recursive CTE under Ipython notebook. > > Finally! We can solve sudoku puzzles in SQL :P > Dan Kennedy, who

Re: [sqlite] "Common Table Expression"

On Jan 17, 2014, at 7:47 PM, big stone wrote: > - I just did my first recursive CTE under Ipython notebook. Finally! We can solve sudoku puzzles in SQL :P http://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/ >

Re: [sqlite] "Common Table Expression"

Yes ! It's in trunk. With Keith Medcalf's help, I suceeded to compile it. => Thanks a lot Keith ! I can confirm : - the sqlite3.dll works under python3 by swapping the default one, - I just did my first recursive CTE under Ipython notebook. ==> I would never have imagined that to happen so

Re: [sqlite] "Common Table Expression"

2013/12/31 big stone : > Hello "CTE in SQLite" fans, > > To get CTE in SQLite, I guess we must answer by the example the fears > expressed by Simon and Rsmith. Not necessary. I noticed that CTE was just merged to SQLite's trunk, so it apparently will be part of SQLite 2.8.3.

Re: [sqlite] "Common Table Expression"

The interest of this "forward lateral" move was its good standardization/effort ratio. For performance/effort ratio, I would have expect people to push SQLite4 and its 2x to 10x promise. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] "Common Table Expression"

Hello, On Jan 6, 2014, at 6:51 AM, James K. Lowden wrote: > You're welcome to your opinion, of course. But you're really not answering > my point, and I object to your assertion that I'm clinging to 1986. Apologies about that. The 1986 reference was more pointed

Re: [sqlite] "Common Table Expression"

On Jan 4, 2014, at 11:34 PM, RSmith wrote: > . I think in America the term "Captain Obvious" is used for the author of > such a statement. This sounds like a job for ObviousMan! http://treesflowersbirds.files.wordpress.com/2010/01/obviousman.jpg > things _are_ moving

Re: [sqlite] "Common Table Expression"

On 2014/01/05 00:03, Petite Abeille wrote: Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause is a change for the better. As is merge. As are windowing functions. SQLite cannot pretend it’s 1986 forever. It has to move with the times or it will become ossified,

Re: [sqlite] "Common Table Expression"

On Jan 4, 2014, at 8:05 PM, James K. Lowden wrote: > That's an aesthetic judgement. Even if I agreed, it doesn't change the > fact that every language feature is an element of complexity, > and redundant language features are needless complexity. Things change.

Re: [sqlite] "Common Table Expression"

On Wed, 1 Jan 2014 23:23:04 +0100 Petite Abeille wrote: > Yes, a ?with? clause is just syntax sugar providing named subqueries. > But this sugar open the door to drastically different ways to write > queries, bringing structure, clarity of thoughts and purpose to >

Re: [sqlite] "Common Table Expression"

On Thu, 2 Jan 2014 01:29:52 + Simon Slavin wrote: > Hmm. Even > > update t set i = i + 1 - 1 > > with i being UNIQUE might be a good test case. Well, that actually works: sqlite> create table t (t int primary key); sqlite> insert into t values (1); sqlite> insert

Re: [sqlite] "Common Table Expression"

(sorry keyboard fall on the floor) Now : - I wouldn't let someone use SQL without allowing him to use CTE, - I need to use ubiquitous SQL motors, which are only TWO on windows (Access and SQLite) , and they still don't have CTE. ___ sqlite-users mailing

Re: [sqlite] "Common Table Expression"

Hello, When bigger brains created CTEs in SQL:99, I suppose they discussed a long moment the technical merit of CTEs. In my own experience : - the maintenance burden of my queries dropped significantly because of them, - I stopped harrassing (myself or a central database administrator) for views

Re: [sqlite] "Common Table Expression"

On 1 Jan 2014, at 9:55pm, James K. Lowden wrote: > sqlite> update i set i = random(); Yeah. You have to do it properly. Mark the rows which will be obsoleted, make up the new rows, and check the resulting union for consistency. Which means that you need to

Re: [sqlite] "Common Table Expression"

On Jan 1, 2014, at 10:55 PM, James K. Lowden wrote: >> CTE ... bring capabilities to the users, by simplifying >> the use of the underlying tool. > > CTEs would add complexity, not simplify. Nonsense, dear Sir :) Yes, a ‘with’ clause is just syntax sugar providing

Re: [sqlite] "Common Table Expression"

On Wed, 1 Jan 2014 11:04:57 +0100 big stone wrote: > You're right : > *"*CTEs ... add exactly zero to SQLite's capability." > > This is also right : > "C Language ... add exactly zero to Intel X86 processor capability". Dennis Ritchie said C is an idealized assembler over

Re: [sqlite] "Common Table Expression"

On Tue, 31 Dec 2013 22:20:15 + Simon Slavin wrote: > > Meanwhile, here's a much more important failing that cannot be > > worked around within SQL without a temporary table: > > > > sqlite> create table i ( i int primary key ); > > sqlite> insert into i values

Re: [sqlite] "Common Table Expression"

You're right : *"*CTEs ... add exactly zero to SQLite's capability." This is also right : "C Language ... add exactly zero to Intel X86 processor capability". In both case : - "adding zero capability" to the underlying tool is a physical constraint, - CTE (or C Language) bring capabilities to

Re: [sqlite] "Common Table Expression"

On Tue, Dec 31, 2013 at 8:59 PM, James K. Lowden wrote: > Recursive queries are a unique feature of CTEs. They are not supported > in SQLite. If and when they are, CTEs will be required. > Not necessarily. Oracle has had the START WITH CONNECT BY syntax for recursive

Re: [sqlite] "Common Table Expression"

Hi James K. Lowden, You're right : *"*CTEs ... add exactly zero to SQLite's capability." This is also right : "C Language ... add exactly zero to Intel X86 processor capability". In both case : - "adding zero capability" to the underlying tool is a physical constraint, - CTE (or C Language)

Re: [sqlite] "Common Table Expression"

On 31 Dec 2013, at 10:05pm, James K. Lowden wrote: > Meanwhile, here's a much more important failing that cannot be worked > around within SQL without a temporary table: > > sqlite> create table i ( i int primary key ); > sqlite> insert into i values (1);

Re: [sqlite] "Common Table Expression"

On Tue, 31 Dec 2013 20:43:20 +0100 big stone wrote: > To get CTE in SQLite, I guess we must answer by the example the fears > expressed by Simon and Rsmith. > > I propose the following method : > - unproven-demand : ... > - code size + performance increase fear : It

Re: [sqlite] "Common Table Expression"

Hello "CTE in SQLite" fans, To get CTE in SQLite, I guess we must answer by the example the fears expressed by Simon and Rsmith. I propose the following method : - unproven-demand : ==> publish on this mailing list external "CTE for SQLite" implementations, ==> if there is demand :

Re: [sqlite] "Common Table Expression"

On Thu, 26 Dec 2013 20:23:33 +0100 big stone wrote: > Indeed, '1' CTE can be replaced by the creation of 'N' temporary > views (or tables), and their deletion after the CTE request. > > CTE is : > - a syntaxic simplification : >. the SQL creator don't have to care

Re: [sqlite] "Common Table Expression"

I agree with David, CTE is just wonderful, a big help to avoid re-typing many times the same sub-query and a performance improvement as well. Yes we can workaround it but it is ugly and leads to un-maintainable code. I am using it every days in my job and I can just tell that it has been proven to

Re: [sqlite] "Common Table Expression"

As a proof of concept, I programmed a translation layer from "CTE" to "SQLite" : "with x as (y)... select z" into "drop view if exists x;create temp view x as y; ...; select z" "with x(d) as (y), ... select z" into "drop table if exists x;create temp table x(d) as y;insert into x y; ...

Re: [sqlite] "Common Table Expression"

RSmith - I said "often", not "entirely". :) Discussion about how to better use SQLite for an already working implementation or for a proposed implementation is a great and proper use of the list. Coming onto the list and asking how to store a simple branch-and-leaf tree structure in SQL is

Re: [sqlite] "Common Table Expression"

Hi again, I know the focus of SQLite people on "size", and "testing". Wouldn't a basic implementation of CTE in fact : - help the "size" of the global embedded system to be smaller ? - without impacting the "testing" ? Indeed, a "simple" implementation of CTE : * could rely only on SQLite

Re: [sqlite] "Common Table Expression"

Sorry, this struck a bit of a sore spot with me, so I apologize for the small rant... Feel free to completely ignore it. You have every right challenging the views of anyone - It is welcome even (I think - cannot speak for everyone else though, but I appreciate it). A rant however is

Re: [sqlite] "Common Table Expression"

to:sqlite-users-boun...@sqlite.org] On Behalf Of RSmith Sent: Thursday, December 26, 2013 5:37 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] "Common Table Expression" This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant i

Re: [sqlite] "Common Table Expression"

This reminds me of a plan to add RADAR dishes to cars to monitor other traffic and avoid collisions - brilliant idea but the detrimental effect on aerodynamics and limiting size-factor of already-built garages all over the world stifled enthusiasm. Probably "Temporary Views" would be the exact

Re: [sqlite] "Common Table Expression"

On 26 Dec 2013, at 7:23pm, big stone wrote: > '1' CTE can be replaced by the creation of 'N' temporary views (or > tables), and their deletion after the CTE request. Just a quick clarification that a VIEW does not greatly increase the amount of data stored in a database.

Re: [sqlite] "Common Table Expression"

Hi again, Indeed, '1' CTE can be replaced by the creation of 'N' temporary views (or tables), and their deletion after the CTE request. CTE is : - a syntaxic simplification : . the SQL creator don't have to care about those intermediate views, . these intermediate views don't grow and

Re: [sqlite] "Common Table Expression"

On 26 Dec 2013, at 6:57pm, big stone wrote: > "sub-select" method : > - allows you to do only a part of what you can do in CTE, > - becomes more and more difficult to read as you add tables and treatment > in your sql. > > With CTE in SQLite, it would be possible to: > -

Re: [sqlite] "Common Table Expression"

Hi again, "sub-select" method : - allows you to do only a part of what you can do in CTE, - becomes more and more difficult to read as you add tables and treatment in your sql. With CTE in SQLite, it would be possible to: - decompose your SQL treatment in clean intermediate steps, - make your

Re: [sqlite] "Common Table Expression"

On 26 Dec 2013, at 3:27pm, big stone wrote: > Does SQLite plan to implement soon a "Common Table Expression" subset ? Common Table Expressions are implemented as sub-SELECTs, as documented in the 'select-stmt' part of this page:

[sqlite] "Common Table Expression"

Hello, Does SQLite plan to implement soon a "Common Table Expression" subset ? CTE would allow to write much more readable SQLite 'SQL' code. It doesn't seem complex to do, as long as the 'RECURSIVE' part of CTE is ignored. Regards, ___ sqlite-users