[sqlite] Efficient random sampling in a large table using builtin functions.

conditions. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient random sampling in a large table using builtinfunctions.

On Thu, Mar 8, 2012 at 15:02, Igor Tandetnik itandet...@mvps.org wrote: Benoit Mortgat mort...@gmail.com wrote: * Generate N, random, row numbers between 1 and (SELECT COUNT(*) FROM   the_table_name). Maybe using remainder operator % and builtin ABS()   and RANDOM() functions can help (see

Re: [sqlite] compressing BLOB

-users -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite shell dot commands

it be possible at least to specify in the output of .help how those arguments are tokenized? (Note: another workaround to import into Ben's table is using the octal digits escaping with backslashes:) .import my_file \042Ben's\040table\042 -- Benoit Mortgat 20, avenue Marcel Paul 69200

Re: [sqlite] SQLite shell dot commands

characters table names should include. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite shell dot commands

? Potential problem: how to use .backup or .restore with a file name that contains both ' and space? Thanks. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] double_quote(text_field)

something like SELECT ''||text_field||'' FROM table; so that your column's data is quoted? -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

On Tue, Feb 22, 2011 at 14:17, Richard Hipp d...@sqlite.org wrote: The query is really more like this: SELECT DISTINCT COALESCE(a.xxx, b.value) value  FROM tbl1 a  LEFT OUTER JOIN tbl2 b    ON a.zzz = b.ttt  WHERE value NOT IN (        SELECT DISTINCT ggg          FROM tbl3       );

[sqlite] SQLite 3.7.5 : same query written using NOT IN and EXCEPT does not give out same results

Hello, I have come across a strange behaviour of SQLite 3.7.5. The following query: SELECT DISTINCT COALESCE(a.xxx, b.yyy) value FROM tbl1 a LEFT OUTER JOIN tbl2 b ON a.zzz = b.ttt EXCEPT SELECT DISTINCT ggg value FROM tbl3; will not return any results (which seems to be correct).

Re: [sqlite] DB file locked when no other process using it

-- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what could be the reason that natural join stops working ?

). I test that by doing an explicit join select Header from vraag join vraaglist on vraag.vlid = vraaglist.vlid where Nr = 0 and vraaglist.Name = 'eortc_br23' Are you sure that after altering your tables adding columns, natural join still only joins on vlid? -- Benoit Mortgat 20, avenue

Re: [sqlite] Upon table creation, multiple CHECK constraints do not work with FTS3

...@gmail.com wrote: On Aug 30, 2010, at 12:22 PM, Benoit Mortgat wrote: Hello, The following table creation fails under latest release : CREATE VIRTUAL TABLE foo USING FTS3 ( bar TEXT, othercolumns TEXT, CHECK(1), CHECK(1) ); Error message: vtable constructor failed Maybe

[sqlite] Upon table creation, multiple CHECK constraints do not work with FTS3

SQLite normally does according to http://osdir.com/ml/sqlite-users/2009-04/msg00279.html using the R*Tree module this syntax works, so the bug should be in the FTS3 module. Thank you, Benoit -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23

Re: [sqlite] How to update many rows efficiently?

and not t1. -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to update many rows efficiently?

, and id_pk must be declared as primary key. Regards, -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] count distinct group by

 having min(r1)=max(r1) and min(r2)=max(r2) ); Another possibility: having count(distinct r1) = 1 and count(distinct r2) = 1 -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing

Re: [sqlite] Query critique

either on player column or friend column in your second table. CREATE INDEX i_friendTable ON friendTable (player ASC); -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list

[sqlite] SQLite lets selecting a column that is not in the Group by clause

or an unknown one? If this is a feature, is there a pragma to forbid such a syntax? Have a nice day -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org