Re: [sqlite] [OT] Re: i Know i should use 'AS', but ....
Using '.mode column" in conjunction with ".headers on" you're already using makes it a lot more obvious. On Mon, Dec 24, 2018, 12:20 PM Luuk On 24-12-2018 19:21, Peter Johnson wrote: > > The headers are present in all three queries you pasted. > > > > The first result shows two rows, the top row is the header. > > > > The other two results show 4 rows each, the top row of each is the header > > row. > > > > -P > > > > On Tue, 25 Dec 2018, 3:42 AM Luuk > > >> sqlite> .version > >> SQLite 3.26.0 > >> > >> sqlite> .headers on > >> > >> sqlite> select 1 as X,date(); > >> X|date() > >> 1|2018-12-24 > >> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x > >> union all select 2 union all select 3); > >> x|row_number() over (order by 1 desc) > >> 3|1 > >> 2|2 > >> 1|3 > >> > >> Why are the headers missing in above query? > >> > > > Why was i overlooking this? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OT] Re: i Know i should use 'AS', but ....
On 24-12-2018 19:21, Peter Johnson wrote: The headers are present in all three queries you pasted. The first result shows two rows, the top row is the header. The other two results show 4 rows each, the top row of each is the header row. -P On Tue, 25 Dec 2018, 3:42 AM Luuk sqlite> .version SQLite 3.26.0 sqlite> .headers on sqlite> select 1 as X,date(); X|date() 1|2018-12-24 sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x union all select 2 union all select 3); x|row_number() over (order by 1 desc) 3|1 2|2 1|3 Why are the headers missing in above query? Why was i overlooking this? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Seasonal syntax
Some SQL terminology: Selection Clause: WHERE Sort Clause: ORDER BY Sublist Clause: LIMIT OFFSET Subsort Clause: GROUP BY HAVING Santa Clause: SELECT name,hobbies,address FROM people WHERE behaviour='nice’ Season’s greetings and best wishes to all subscribers. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] i Know i should use 'AS', but ....
The headers are present in all three queries you pasted. The first result shows two rows, the top row is the header. The other two results show 4 rows each, the top row of each is the header row. -P On Tue, 25 Dec 2018, 3:42 AM Luuk sqlite> .version > SQLite 3.26.0 > > sqlite> .headers on > > sqlite> select 1 as X,date(); > X|date() > 1|2018-12-24 > sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x > union all select 2 union all select 3); > x|row_number() over (order by 1 desc) > 3|1 > 2|2 > 1|3 > > Why are the headers missing in above query? > > > > sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1 > as x union all select 2 union all select 3); > x|Y > 3|1 > 2|2 > 1|3 > sqlite> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sample Employee database ported to SQLite from MySQL
On Sun 23 Dec 2018 7:15 PM, Larry Brasfield wrote: > Jungle Boogie wrote: > ➢ Anyone else have issues decompressing the file? $ bzip2 -d employees.db.bz2 > bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 employees.db.bz2 > bunzip2: employees.db.bz2 is not a bzip2 file. $ file employees.db.bz2 > employees.db.bz2: Non-ISO extended-ASCII HTML document text $ sha256 > employees.db.bz2 SHA256 (employees.db.bz2) = > 2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2 > > I browsed to the web page, link to which you quoted. At that page (on > GitHub) I clicked the “Clone or download” button, then clicked the “Download > ZIP” option, whereupon a .zip file could be downloaded. Within that .zip > archive, in a subdirectory, was a file which appeared as follows to the > ‘file’ utility: > > file employees.db > employees.db: SQLite 3.x database I think my problem that I was using the wrong URL. Correct URL to fetch is this: https://github.com/siara-cc/employee_db/raw/master/employees.db.bz2 Thanks Larry and Arun. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.26.0 recursive CTE performance regression
Am 24.12.2018 um 13:12 schrieb Richard Hipp: There are now enhancements on a branch (https://www.sqlite.org/src/timeline?r=reuse-subqueries) that should fix your performance problem. Since you seem to be someone who writes intense SQL, it would be really cool if you could try out that branch and see if it solves or causes any other problems. Thank a lot. After compiling at check-in 06de44ec, the example query as well as my full query are back to the execution time from 3.25.3 again. While the variant proposed by Jake is faster and simpler for getting just the paths (thanks for that), the full query includes additional things like selecting properties of certain ancestors along the path. Best, Sebastian ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] i Know i should use 'AS', but ....
sqlite> .version SQLite 3.26.0 sqlite> .headers on sqlite> select 1 as X,date(); X|date() 1|2018-12-24 sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x union all select 2 union all select 3); x|row_number() over (order by 1 desc) 3|1 2|2 1|3 Why are the headers missing in above query? sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1 as x union all select 2 union all select 3); x|Y 3|1 2|2 1|3 sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.26.0 recursive CTE performance regression
On 12/22/18, Sebastian Bank wrote: > > given a table that represents an adjacency tree, I use a recursive CTE > together with group_concat() to generate the path for each tree item. > > With SQLite up to version 3.25.3 the query below (with the 500 example > items inserted below) takes about 0.2 seconds on my system. With version > 3.26.0 it takes over 6 seconds (with the full data set of around 24000 > items, it seems to become infeasible). There are now enhancements on a branch (https://www.sqlite.org/src/timeline?r=reuse-subqueries) that should fix your performance problem. Since you seem to be someone who writes intense SQL, it would be really cool if you could try out that branch and see if it solves or causes any other problems. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
