[sqlite] Process duplicate field values

On 2/19/2016 8:16 PM, admin at shuling.net wrote: > No. Originally I think since task 2 and 3 are operations performed on the > same set of records, maybe they can be merged to improved the performance > though one is get and another is set. Ah. Perhaps you are looking for sqlite3_changes() API

[sqlite] Process duplicate field values

This is called premature optimization. It is the root of all Evil. In DonaldKnuth's paper "StructuredProgrammingWithGoToStatements", he wrote: "Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at

[sqlite] Process duplicate field values

Hi, Thank you. In that case, is there a way to create a table which contains only the records that have duplicate F1 values? Thanks > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of R

[sqlite] [BUG on version 3110000] link error: undefined reference to 'explainIndexColumnName'

building version 311 with the following options: -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -O2 -fno-stack-protector -fomit-frame-pointer -DUSE_PREAD -DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_LOAD_EXTENSION

[sqlite] Multiple Column index

I supposed your SQL command like this "select * from table where (DateTime>X AND DateTimeX AND DateTimemailto:michele.pradella at selea.com> : ?2016/?2/?19 16:06 ???: sqlite-users at mailinglists.sqlite.org ??: [sqlite] Multiple

[sqlite] Process duplicate field values

Hi Sorry for the confusion. The distinct values for F1 is only 1, 2 and 3, so the distinct value count is 3. However, there are totally 6 records that have duplicate F1 values. The task 2 is to calculate the total count of conflict records so it is 6. Hope that explain the issue clearly.

[sqlite] SQLite as calculator

Yes it is expected. Strings always compare greater than numbers. Arithmetic operators will apply numeric affinity to their operands, allowing e.g. the addition of a string and an integer to produce the expected result. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at

[sqlite] Process duplicate field values

Hi, Thank you very much. Can task 2 and 3 be performed in one query to improve the performance? Thanks > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter > Sent: Friday,

[sqlite] sqlite in vba7.1 and windows 8.1

Hello, I've been using sqlite in vba6 for some years using RichClient3 framework of Olaf Schmidt (www.datenhaus.de) Now I'm migrating my application to 64 bits using vba7 in Windows 8.1. The problem I'm facing is with the LoadLibrary function to load dynamically the DLL DirectCOM.dll The

[sqlite] Process duplicate field values

Hi, I create a table as follows: CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER); Then add the following records: INSERT INTO MyTable (F1, F2) Values (1, 2); INSERT INTO MyTable (F1, F2) Values (1, 3); INSERT INTO MyTable (F1, F2) Values (2, 4); INSERT INTO MyTable (F1, F2) Values (2,

[sqlite] Multiple Column index

On 19 Feb 2016, at 10:34am, Michele Pradella wrote: > Ok but the question is: can a statement (SELECT * FROM table WHERE (field1 > LIKE 'TEXT%')AND(field2>=X)AND(field2<=Y)) use the index created on > multicolumn field1,field2? or I use it only if I have file1 IN,=,IS? > Which is the best

[sqlite] Bug (?) with DateTime in Windows Phone 8.1

On 17 Feb 2016, at 4:00pm, Lionel Tranchand / FH SARL wrote: > If the user choose the indonesian langage as the system langage and stores a > date time with the local format, the time will be stored as a string as 12.00 > for example, not 12:00. Most programming langauges/APIs have two

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

-Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von nomad at null.net Gesendet: Freitag, 19. Februar 2016 11:01 An: SQLite mailing list Betreff: [sqlite] EXPLAIN QUERY PLAN for non-SELECT

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

nomad at null.net wrote: > I wanted to see how SELECT queries within triggers are using indexes > (or not) which of course lead me to https://www.sqlite.org/eqp.html: > > EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may > also be appear with other statements that read data

[sqlite] Process duplicate field values

On 2016/02/19 8:00 AM, admin at shuling.net wrote: > Hi, > > > > I create a table as follows: > > > > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER); > > > > Then add the following records: > > > > INSERT INTO MyTable (F1, F2) Values (1, 2); > > INSERT INTO MyTable (F1, F2) Values (1,

[sqlite] Multiple Column index

Michele Pradella wrote: >> Michele Pradella wrote: >>> so there's no way to use that kind of double column index on a select like >>> explain query plan select * from test where (CarPlate LIKE 'AA000%') AND >>> (DateTime>1); >>> because at least one field have to do with operator = correct? >> >>

[sqlite] Multiple Column index

> (please don't top-post) > > Michele Pradella wrote: >> so there's no way to use that kind of double column index on a select like >> explain query plan select * from test where (CarPlate LIKE 'AA000%') AND >> (DateTime>1); >> because at least one field have to do with operator = correct? > No,

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

I wanted to see how SELECT queries within triggers are using indexes (or not) which of course lead me to https://www.sqlite.org/eqp.html: EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also be appear with other statements that read data from database tables (e.g.

[sqlite] NuGet problem

Not much help for an automated build. That's what I use now, and I *hate* having to check third party DLLs into the source code tree. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org

[sqlite] SQLite as calculator

We are using SQLite to do some simple expression evaluation in our app. In so doing - we have notices the following: sqlite> SELECT '1' > 100; 1 sqlite> SELECT '1' + 100; 101 From my point of view this seems somewhat inconsistent. I understand I am not really using SQLite in a way it was

[sqlite] Process duplicate field values

On 2/19/2016 1:00 AM, admin at shuling.net wrote: > 1. For all conflict records, get the total count of distinct F1 values. > In the above sample, record 1, 2, 3, 4, 5, 6 are conflict records, but the > distinct values are only 1, 2, 3 so the total count should be 3. > 2. Get the total

[sqlite] determining is-leap-year in sqlite

My SQLite cte exercise, the output looks like cal in Linux shell: -- with recursive parameters(aday) as ( select date() ), months(n, nm) as ( values(1,'January'),(2,'February'),(3,'March'),(4,'April'),(5,'May'),(6,'June'),

[sqlite] Multiple Column index

(please don't top-post) Michele Pradella wrote: > so there's no way to use that kind of double column index on a select like > explain query plan select * from test where (CarPlate LIKE 'AA000%') AND > (DateTime>1); > because at least one field have to do with operator = correct? No, the number

[sqlite] Process duplicate field values

ed with OpenPGP using GPGMail URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160219/9097ad06/attachment.pgp>

[sqlite] Multiple Column index

because at least one field have to do with operator = correct? no can be one of = or IN or IS but not LIKE operator Selea s.r.l. Michele Pradella R SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.pradella at

[sqlite] Multiple Column index

Ok understand, so there's no way to use that kind of double column index on a select like explain query plan select * from test where (CarPlate LIKE 'AA000%') AND (DateTime>1); because at least one field have to do with operator = correct? Selea s.r.l. Michele Pradella R

[sqlite] Multiple Column index

Michele Pradella wrote: > CREATE TABLE test (DateTime BIGINT,CarPlate VARCHAR(255)); > CREATE INDEX indexA ON test(DateTime); > CREATE INDEX indexB ON test(CarPlate); > CREATE INDEX indexAB ON test(DateTime,CarPlate); > > EXLPAIN QUERY PLAN SELECT * FROM test WHERE (DateTime>1)AND(DateTime<100)

[sqlite] Multiple Column index

Your indexes are badly designed. You require the following two indexes: CREATE INDEX indexAB ON test(DateTime,CarPlate); CREATE INDEX indexBA ON test(CarPlate,DateTime); The indexes: > CREATE INDEX indexA ON test(DateTime); > CREATE INDEX indexB ON test(CarPlate); serve no useful purpose and

[sqlite] Multiple Column index

Hi all, I have a question about using index with multiple column. Take this test case CREATE TABLE test (DateTime BIGINT,CarPlate VARCHAR(255)); CREATE INDEX indexA ON test(DateTime); CREATE INDEX indexB ON test(CarPlate); CREATE INDEX indexAB ON test(DateTime,CarPlate); now if you do [1] ->

[sqlite] Multiple Column index

The order of fields in the multicolumn index matters. Taken in the order of fields in the index, the first inequality relation terminates the part of the index that is usable. For a constraint "plate EQUALS and date BETWEEN" you need an index on (plate,date). Your index is only usable for

[sqlite] Process duplicate field values

Basic idea, no testing The core query is Select F1 as key,count() as count from MyTable group by F1 having count>1; The number of distinct F1 values is select count() from () The number of conflict records is Select sum(count) from () And the update would be Update MyTable set F2=9 where

[sqlite] determining is-leap-year in sqlite

On Fri, Feb 19, 2016 at 3:03 AM, Quan Yong Zhai wrote: > My SQLite cte exercise, the output looks like cal in Linux shell: > ... > >February 2016 > Su Mo Tu We Th Fr Sa > 1 2 3 4 5 6 > 7 8 9 10 11 12 13 > 14 15 16 17 18 19 20 > 21 22 23 24 25 26 27 > 28 29 > LOL! i needed

[sqlite] determining is-leap-year in sqlite

On Fri, Feb 19, 2016 at 1:53 AM, Stephan Beal wrote: > It can now optionally mark the current date (but this feature slowed it > down from 'instant' to 'just under a second or so', possibly due to SQL > inefficiencies on my part). > Trimming the list of years from 100 years to now +/-5 years

[sqlite] determining is-leap-year in sqlite

On Fri, Feb 19, 2016 at 12:36 AM, k wrote: > On 18/02/2016 21:55, Stephan Beal wrote: > >> >>> http://fossil.wanderinghorse.net/download/cal.sql >>> >>> >> Excellent CTE query, thanks, but one question: the query uses > group_concat() and the documentation says 'The order of the concatenated >