Re: [sqlite] SQL to SQLite

2019-12-03 Thread Keith Medcalf
On Tuesday, 3 December, 2019 05:39, gideo...@lutzvillevineyards.com wrote: >My query is : >UPDATE wbridge_history >SET yearclass = >( >SELECT D.wynklas >FROM >( >SELECT LidNo, PlaasNo, BlokNo, oesjaar, wynklas, >ROW_NUMBER() OVER (PARTITION BY LidNo, PlaasNo, BlokNo, oesjaar ORDER BY >COUNT(*)

Re: [sqlite] PRAGMA for .load

2019-12-03 Thread David Raymond
I _think_ the load_extension() function is what you'll be looking for, though I could be wrong. https://www.sqlite.org/lang_corefunc.html#load_extension -Original Message- From: sqlite-users On Behalf Of Jose Isaias Cabrera Sent: Tuesday, December 3, 2019 3:38 PM To:

[sqlite] PRAGMA for .load

2019-12-03 Thread Jose Isaias Cabrera
Greetings. Quick question... I was looking into the pragmas page, and I don't see one that would do the function to load other libraries, such as the .load function of the CLI. Is there one? Thanks. josé ___ sqlite-users mailing list

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

2019-12-03 Thread Jonathan Moules
Thanks for the suggestion. Changing the USING to ON makes absolutely no difference. The speed is the same and the query plans (both EXPLAIN and EXPLAIN QUERY PLAN) are absolutely identical. Same for if I convert it to WHERE: WHERE joining_table.data_id = data_table.data_id; On 2019-12-03

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

2019-12-03 Thread Simon Slavin
On 3 Dec 2019, at 8:48am, Jonathan Moules wrote: > SELECT > count(1) > FROM > data_table > JOIN joining_table USING (data_id); SELECT count(1) FROM data_table JOIN joining_table ON joining_table.data_id =

Re: [sqlite] SQL to SQLite

2019-12-03 Thread Warren Young
On Dec 3, 2019, at 5:38 AM, gideo...@lutzvillevineyards.com wrote: > > The squigly red line starts at (PARTITION .. PARTITION BY is a valid SQLite window function: https://www.sqlite.org/windowfunctions.html#the_partition_by_clause but that feature is only about a year old in SQLite:

[sqlite] SQL to SQLite

2019-12-03 Thread gideon.e
Hi Simon My query is : UPDATE wbridge_history SET yearclass = ( SELECT D.wynklas FROM ( SELECT LidNo, PlaasNo, BlokNo, oesjaar, wynklas, ROW_NUMBER() OVER (PARTITION BY LidNo, PlaasNo, BlokNo, oesjaar ORDER BY COUNT(*) DESC, SUM(ton) DESC) AS row_num FROM wbridge_history

Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes

2019-12-03 Thread Jonathan Moules
Hi List, So, I've altered my structure to be INTEGER primary keys, but I'm still seeing very slow query times when joining. The original query is faster:   SELECT   count(1)   FROM   data_table   JOIN joining_table USING (data_id); It takes ~2s, but if I then