Re: [sqlite] Select statement with ORDER BY specified by column value

but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Marco Bambini >Sent: Saturday, 29 February, 2020 01:38 >To: SQLite mailing list >Subject: [sqlite] Select statement with ORDER BY specifie

Re: [sqlite] Select statement with ORDER BY specified by column value

On 29 Feb 2020, at 8:37am, Marco Bambini wrote: > ORDER BY (prop_tag='ios') LIMIT 1; > > I would like to prioritise results based on the fact that the prop_tag column > is 'ios'. SQLite has a conditional construction: CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END So do SELECT … ORDER BY

[sqlite] Select statement with ORDER BY specified by column value

Hi all, Is there a way to specify an ORDER BY clause by column value? I have a table declared as: CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, prop_tag TEXT DEFAULT '*', UNIQUE(obj_id, prop_key, prop_tag)) and a sample query:

Re: [sqlite] select for power-meter accumulated total readings

On Sun, Oct 20, 2019 at 8:23 PM Petr Jakeš wrote: > > > On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf wrote: > >> >> On Sunday, 20 October, 2019 06:58, Petr Jakeš >> wrote: >> >> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf >> wrote: >> >> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš

Re: [sqlite] select for power-meter accumulated total readings

On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf wrote: > > On Sunday, 20 October, 2019 06:58, Petr Jakeš > wrote: > > >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf > wrote: > > >> On Saturday, 19 October, 2019 18:26, Petr Jakeš < > petr.jakes@gmail.com> wrote: > > >>> After long time I have

Re: [sqlite] select for power-meter accumulated total readings

On Sunday, 20 October, 2019 06:58, Petr Jakeš wrote: >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf wrote: >> On Saturday, 19 October, 2019 18:26, Petr Jakeš >> wrote: >>> After long time I have set up development environment properly and I >>> am able to start to study your queries. >>>

Re: [sqlite] select for power-meter accumulated total readings

On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf wrote: > On Saturday, 19 October, 2019 18:26, Petr Jakeš > wrote: > > >After long time I have set up development environment properly and I am > >able to start to study your queries. > > >I am lost. I don't either understand the first bunch of

Re: [sqlite] select for power-meter accumulated total readings

On Saturday, 19 October, 2019 18:26, Petr Jakeš wrote: >After long time I have set up development environment properly and I am >able to start to study your queries. >I am lost. I don't either understand the first bunch of subqueries... ( >What is returned in the "ratetoprior"? I have been

Re: [sqlite] select for power-meter accumulated total readings

After long time I have set up development environment properly and I am able to start to study your queries. I am lost. I don't either understand the first bunch of subqueries... ( What is returned in the "ratetoprior"? I have been pulling my hair over 3 hours trying to figure it out ... no clue

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Wednesday, 9 October, 2019 13:04 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't &g

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

eopen on a persistent database. sqlite> create table nodes(name text not null collate nocase unique); sqlite> insert into nodes values ('dangtalk'); sqlite> insert into nodes values ('dingdong'); sqlite> insert into nodes values ('dingwhit'); sqlite> insert into nodes values ('dongdon

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

> On Oct 9, 2019, at 10:02 AM, Keith Medcalf wrote: > > SUBSTR(name, 0, ?) is an expression, so unless you have an index on that > expression, then an index cannot be used to SEARCH for the rows. That's accurate in general. However, there _is_ a very similar special-case optimization for

Re: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Brannon King >Sent: Tuesday, 8 October, 2019 15:53 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't > >I have

[sqlite] SELECT uses index with SUBSTR but UPDATE doesn't

I have this query: UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ? EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the same time, the query plan for this: SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? tells me that it can and will use the (primary key) index on the

Re: [sqlite] select for power-meter accumulated total readings

On Monday, 2 September, 2019 12:26, Petr Jakeš wrote: >Yes, you are right. The error is connected with the version of >SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0. >Than I have to study your code. Your knowledge and SQL Windows >functions are over my scope. Thank

Re: [sqlite] select for power-meter accumulated total readings

Yes, you are right. The error is connected with the version of SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0. Than I have to study your code. Your knowledge and SQL Windows functions are over my scope. Thank for the study material for next weekend :D On Mon, Sep 2, 2019

Re: [sqlite] select for power-meter accumulated total readings

On Monday, 2 September, 2019 10:34, Petr Jakeš wrote: >Wow, this is HUUUDGE !!! >Thanks! >What editor are you using, btw? Typically this is on Windows 10 (for Workstations) and the editor I use is TSE (The Semware Editor). Started using TSE under OS/2 way back and I like it a lot and have

Re: [sqlite] select for power-meter accumulated total readings

Wow, this is HUUUDGE !!! Thanks! What editor are you using, btw? I am on Linux Mint and trying your queries with "SQLite Studio" and "DB Browser for SQLite" is throwing syntax error (I think because of the rows "lead(timestamp) over (order by timestamp) as next_timestamp," From the sqlite3

Re: [sqlite] select for power-meter accumulated total readings

Of course, what we are emulating here is called a "Process Historian", common examples being PHD and PI. So, if you make a few minor adjustments, you can make this run just about as fast as a "designed for purpose" Process Historian. The changes are that you need to store the data in an

Re: [sqlite] select for power-meter accumulated total readings

This will get you the consumption projection for each day in the table (timestamp in s represents the ENDING period you are interested in and you can modify it to whatever interval you want, and of course the final query gets the result). It works by computing the slope from each timestamp to

Re: [sqlite] select for power-meter accumulated total readings

As far I have ended with following: WITH miniPow as ( select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini from power group by date(timestamp) ) , maxiPow as ( select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi from power group by date(timestamp) ) select maxiPow.d, ROUND(maxi-mini, 1) from

[sqlite] select for power-meter accumulated total readings

I am storing electricity consumption data to the sqlite. The simple table to store kWh consumption looks like following example (accumulated total readings in each row - exactly as you see on your electricity meter): |ID|timestamp|kWh ||1 | 2019-07-31 14:24:25 | 270.8||2 |

Re: [sqlite] select within transaction

Thank you, Adrian. I think this is reason changes() exist. Roman Sent from my T-Mobile 4G LTE Device Original message From: Adrian Ho Date: 6/15/19 12:25 AM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] select within transaction On 15/6/19 2

Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote: > I have a transaction consisting of two commands: update and select. The idea > is to get new state after update: > > PRAGMA busy_timeout = 50; > BEGIN EXCLUSIVE; > UPDATE OR ROLLBACK t SET c = 5 WHERE ...; > SELECT d FROM t WHERE c = 5 AND ...; >

Re: [sqlite] select within transaction

On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote: > Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote... > >> Yes, and no. From what I understand, and have been using it, if >> something was written to the DB, it will give you a 1. Otherwise >> a 0. But, it is not the amount of fields,

Re: [sqlite] select within transaction

ubject: Re: [sqlite] select within transaction How are you sending the commands to the cli? If you're doing... sqlite3 myfile.sqlite ".read somefile.sql" ...then you can start the sql file with... .bail on ...and as soon as it hits an error it will stop there and not continue process

Re: [sqlite] select within transaction

formation. It does give you the amount of fields updated. Ie. sqlite> create table a (a, b, c); sqlite> insert into a values (1, 2, 3); sqlite> insert into a values (2, 3, 4); sqlite> insert into a values (3, 4, 5); sqlite> select changes(); 1 sqlite> select total_changes(); 3 sqlite>

Re: [sqlite] select within transaction

back the uncommitted transaction. -Original Message- From: sqlite-users On Behalf Of Roman Fleysher Sent: Friday, June 14, 2019 2:23 PM To: General Discussion of SQLite Database Subject: [sqlite] select within transaction Dear SQLiters, I am using sqlite3 shell. I have a transacti

Re: [sqlite] select within transaction

able a (a, b, c); sqlite> create table b (a, d, e); sqlite> insert into a values (1, 2, 3); sqlite> insert into a values (2, 3, 4); sqlite> insert into a values (3, 4, 5); sqlite> select changes(); -- this is for the last write 1 sqlite> select total_changes(); -- this is for t

[sqlite] select within transaction

Dear SQLiters, I am using sqlite3 shell. I have a transaction consisting of two commands: update and select. The idea is to get new state after update: PRAGMA busy_timeout = 50; BEGIN EXCLUSIVE; UPDATE OR ROLLBACK t SET c = 5 WHERE ...; SELECT d FROM t WHERE c = 5 AND ...; COMMIT; Is this

Re: [sqlite] select * where abc like "xxx" works, ...where abc='xxx' fails

...> dancename text ...> ); sqlite> insert into songfiletable (dancename) values ('Waltz'); sqlite> select * from songfiletable where dancename like 'Waltz'; 1|Waltz sqlite> select * from songfiletable where dancename = 'Waltz'; 1|Waltz Works for me. Perhaps your database does

Re: [sqlite] select * where abc like "xxx" works, ...where abc='xxx' fails

On 4 Jun 2019, at 12:46am, Doug wrote: > Why does the "=" query fail and the "like" query work? To help us investigate ... Which version of SQLite is this ? You can use SELECT sqlite_version(); to find out. What do you mean by 'work' and 'fail' ? Are you referring to an error code ?

[sqlite] select * where abc like "xxx" works, ...where abc='xxx' fails

I am using Sqlite under QT 5.12.0. Why does the "=" query fail and the "like" query work? There are no wildcards involved. I create the table this way: QString sqlcreate = QLatin1String( "CREATE TABLE songfiletable (" "songfile_id INTEGER PRIMARY KEY," "dancename TEXT"

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

N table1.rowid = table2.rowid > >> WHERE table1.name LIKE '%smth%' > >> > >> > >> -Original Message- > >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org > ] > >> On Behalf Of Simon Slavin > >> Sent: Friday,

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

t;> ON table1.rowid = table2.rowid >> WHERE table1.name LIKE '%smth%' >> >> >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Simon Slavin >> Sent: Friday, September 14, 2018 1:59 PM >&g

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

WHERE table1.name LIKE '%smth%' > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Friday, September 14, 2018 1:59 PM > To: SQLite mailing list > Subject: Re: [sqlite] [SQLI

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

o Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Maziar Parsijani >Sent: Friday, 14 September, 2018 12:32 >To: sqlite-users@mailinglists.sqlite.org >Sub

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

Hi, Thanks for your answer.I used your answer like this : SELECT * FROM table2 JOIN table1 on table1.rowid = table2.rowid WHERE table1.name LIKE '%smth%' Because without the "table1 on" statement it didn't work . On Fri, Sep 14, 2018 at 10:29 PM Simon Slavin wrote: > On 14 Sep

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

mailing list Subject: Re: [sqlite] [SQLITE]select from a table and use its data to select from another one On 14 Sep 2018, at 6:50pm, Maziar Parsijani wrote: > I have 2 tables with the same rowid now I want to : > select rowid from table1 where table1 like "%smth%" > select

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

On 14 Sep 2018, at 6:50pm, Maziar Parsijani wrote: > I have 2 tables with the same rowid now I want to : > select rowid from table1 where table1 like "%smth%" > select * from table2 where rowid =(selected rows before) > > I mean if I could do it in a same query. This is what JOIN is for.

[sqlite] [SQLITE]select from a table and use its data to select from another one

Hi, I have 2 tables with the same rowid now I want to : select rowid from table1 where table1 like "%smth%" select * from table2 where rowid =(selected rows before) I mean if I could do it in a same query. ___ sqlite-users mailing list

Re: [sqlite] SELECT becomes very slow when converted to UPDATE

Do I correctly understand the intention of the UPDATE is that for each my_id in meta_table, it will store the count of all the hashes that are associated only with my_id and no other id's? In that case, have you tried: UPDATE meta_table SET distinct_hashes = ( SELECT

[sqlite] SELECT becomes very slow when converted to UPDATE

Hi List, I'm trying to find all hashes that are unique to a specific id (my_id), and then use a UPDATE-Join to update another table with that number. After much tweaking, I've simplified the table down to a basic temp table (actually created using a CREATE AS SELECT ... GROUP BY my_id, hash):

Re: [sqlite] SELECT with CASE

2018-04-12 21:09 GMT+02:00 Csányi Pál : > Thank you very much for the help and for the explanations. > > Waw! It is so complicated at first! I hope I shall understand these soon. > > Finally I decide to use this query: > SELECT Keltezes FROM Orak WHERE Keltezes >=

Re: [sqlite] SELECT with CASE

Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >>Sent: Thursday, 12 April, 2018 10:24 >>To: SQLite mailing list >>Subject: Re: [sqlite] SELECT with CASE >> >>On 12 Apr 2018, at 5:16pm, R Smith

Re: [sqlite] SELECT with CASE

but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Thursday, 12 April, 2018 10:24 >To: SQLite mailing list >Subject: R

Re: [sqlite] SELECT with CASE

On 12 Apr 2018, at 5:16pm, R Smith wrote: > SELECT MIN(TheDate) -- get the smallest date > FROM Orak -- from the table with School-days > WHERE TheDate >= date('now') -- where the school-day is later or equal to > today. > ; This

Re: [sqlite] SELECT with CASE

You're right. I am developing an Android app on App Inventor2. The app is in Hungarian language so the SQLite database contains tables and columns with Hungarian names. The whole schema is like this: CREATE TABLE Beiratkozottak( az INTEGER PRIMARY KEY UNIQUE, TanuloNeve TEXT NOT NULL,

Re: [sqlite] SELECT with CASE

(tempDate) as TheDate from foo; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál Sent: Thursday, April 12, 2018 11:36 AM To: SQLite mailing list Subject: Re: [sqlite] SELECT with CASE Thank you very much! Just can't

Re: [sqlite] SELECT with CASE

--- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >Sent: Thursday, 12 April, 2018 09:36 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE > >Thank you very much! > >Just can't understand why the CASE met

Re: [sqlite] SELECT with CASE

Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= date('now'); On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" wrote: Ah, so if there's two days in a row that

Re: [sqlite] SELECT with CASE

Ah, so if there's two days in a row that aren't school days, you need to be able to select a day two or more days in the future. On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" wrote: So when I

Re: [sqlite] SELECT with CASE

On 2018/04/12 5:35 PM, Csányi Pál wrote: Thank you very much! Just can't understand why the CASE method does not work? It can't be done with the CASE expression at all? The CASE expression modifies a single line, the WHERE clause restricts the selection to the lines that qualify. So if you

Re: [sqlite] SELECT with CASE

Hi Ryan, 2018-04-12 17:36 GMT+02:00 R Smith : > On 2018/04/12 5:20 PM, Csányi Pál wrote: >> >> Yes, this is what I am asking. >> >> 2018-04-12 17:17 GMT+02:00 Keith Medcalf : >>> >>> Which seems like a rather long winded way of stating the problem: >>>

Re: [sqlite] SELECT with CASE

ite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Thursday, 12 April, 2018 09:26 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE > > >Then Richard is correct (of course) ... which is a perfect >translation of the problem statement into

Re: [sqlite] SELECT with CASE

On 2018/04/12 5:20 PM, Csányi Pál wrote: Yes, this is what I am asking. 2018-04-12 17:17 GMT+02:00 Keith Medcalf : Which seems like a rather long winded way of stating the problem: "I have a table with a bunch-o-dates in it. I want a query which will return, at the time

Re: [sqlite] SELECT with CASE

; >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >>Sent: Thursday, 12 April, 2018 09:20 >>To: SQLite mailing list >>Subject: Re: [sqlite] SELECT with CASE >> >>Yes,

Re: [sqlite] SELECT with CASE

a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >Sent: Thursday, 12 April, 2018 09:20 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE > &g

Re: [sqlite] SELECT with CASE

sage- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >>Sent: Thursday, 12 April, 2018 09:10 >>To: SQLite mailing list >>Subject: Re: [sqlite] SELECT with CASE >> >>2018-04-12 17:08 GMT+02:00 Keith

Re: [sqlite] SELECT with CASE

ys a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >Sent: Thursday, 12 April, 2018 09:10 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE >

Re: [sqlite] SELECT with CASE

On 4/12/18, Csányi Pál wrote: > 2018-04-12 17:08 GMT+02:00 Keith Medcalf : >> >> select TheDate from Dates where TheDate == date('now'); > > Yes, but I want the CASE because if there is no such date in the Dates > table which is equal to the date('now')

Re: [sqlite] SELECT with CASE

2018-04-12 17:08 GMT+02:00 Keith Medcalf : > > select TheDate from Dates where TheDate == date('now'); Yes, but I want the CASE because if there is no such date in the Dates table which is equal to the date('now') then it should return the date('now','+1 day').

Re: [sqlite] SELECT with CASE

On Behalf Of Csányi Pál >Sent: Thursday, 12 April, 2018 09:06 >To: SQLite mailing list >Subject: Re: [sqlite] SELECT with CASE > >2018-04-12 17:00 GMT+02:00 Peter Da Silva ><peter.dasi...@flightaware.com>: >> One of the lines of the output does indeed have '2018-04-12' as

Re: [sqlite] SELECT with CASE

2018-04-12 17:00 GMT+02:00 Peter Da Silva : > One of the lines of the output does indeed have '2018-04-12' as expected. Indeed, I did not notice. Then how can I get only that date from the Dates table - which is equal to the current date?

Re: [sqlite] SELECT with CASE

One of the lines of the output does indeed have '2018-04-12' as expected. On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" wrote: 2018-04-12 16:51 GMT+02:00 Peter Da Silva

Re: [sqlite] SELECT with CASE

2018-04-12 16:51 GMT+02:00 Peter Da Silva : > You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is > what you're getting, no? Yes, indeed. But I thought the first part would be done: CASE TheDate WHEN date('now') THEN TheDate that is, if the

Re: [sqlite] SELECT with CASE

You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what you're getting, no? On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" wrote: Hi Simon, 2018-04-12 14:32

Re: [sqlite] SELECT with CASE

Hi Simon, 2018-04-12 14:32 GMT+02:00 Simon Slavin : > On 12 Apr 2018, at 1:25pm, Csányi Pál wrote: > >> SELECT CASE TheDate = date('now') WHEN TheDate ... > > I don't think that's what you wanted. Perhaps > > SELECT CASE TheDate WHEN date('now') ... >

Re: [sqlite] SELECT with CASE

On 12 Apr 2018, at 1:25pm, Csányi Pál wrote: > SELECT CASE TheDate = date('now') WHEN TheDate ... I don't think that's what you wanted. Perhaps SELECT CASE TheDate WHEN date('now') ... But you should test the output of "date('now')" to make sure it is in the format you

[sqlite] SELECT with CASE

Hi, I have a small database: DatesOfYear.db with only one table: CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL); I insert into the table some datas with: INSERT INTO Dates VALUES(1,'2018-04-01'); INSERT INTO Dates VALUES(2,'2018-04-02'); INSERT INTO Dates

Re: [sqlite] Select query does not give new added records

On 31 Mar 2018, at 2:04pm, Koen Amant wrote: > there is a service running in the background who adds records > to the database (POS system) I can't stop this service and all the new > records that are added I can't see in my query result. It's like the > database is locked

[sqlite] Select query does not give new added records

I have a table with 9 records. When I run 'SELECT * FROM table;' in my VB.Net I only get 6 from the 9 records. When I run the query in 'DB Browser' an SQLite viewer I get them all. The thing is there is a service running in the background who adds records to the database (POS system) I can't

Re: [sqlite] Select WHERE IN List ordering

You saved my bacon with this one. Just wanted to pop in and say a quick thanks to you. :) -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] SELECT query #first run# is very slow VOL2

On 19 Dec 2017, at 8:37pm, zakari wrote: > pasting some logs, Im declaring again this happening only the first time, > afterwards working without problem. > 2017-12-17 15:16:23 - execute > 2017-12-17 15:17:20 - executed > > 2017-12-19 14:53:35 - execute > 2017-12-19

[sqlite] SELECT query #first run# is very slow VOL2

hi all, I have exactly the same problem with topic : http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html -- The dbase sitting on linux server, Im accessing the dbase with PDO object. -connected -prepare the statement -execute here makes =>1min lag, *only the

Re: [sqlite] SELECT result different after ANALYZE

On 21.11.2017 15:36, Richard Hipp wrote: I'll be working on some other solution for you. Many thanks, but this is not necessary. I can rebuild from Fossil. Ralf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] SELECT result different after ANALYZE

On 11/21/17, Richard Hipp wrote: > > To work around this problem, please DROP all indexes on the INTEGER > PRIMARY KEY columns. Except, you don't have any indexes on INTEGER PRIMARY KEY columns. I misread the schema. I'll be working on some other solution for you. -- D.

Re: [sqlite] SELECT result different after ANALYZE

On 11/20/17, David Raymond wrote: > > To reproduce, download this database file (5.6MB, SHA1 > 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now): > >https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html > > This SQL returns a

Re: [sqlite] SELECT result different after ANALYZE

Apologies for the Spam, and this may be of no importance whatsoever, but just in case it is useful... I already mentioned that dropping/messing with the sqlite_stat1 table doesn't help - BUT it seems if you close the connection and re-open in a new connection (after you have dropped the

Re: [sqlite] SELECT result different after ANALYZE

Just to Add to what Ralf and David already pointed out: Works for me on 3.18, not in 3.20.1 and more importantly,  the sqlite_stat1 table itself seems to have zero impact, once Analyze is run, the query always does not work, even if you drop the sqlite_stat1 table or mess with its values.

Re: [sqlite] SELECT result different after ANALYZE

3.18.0 gets it correct, 3.19.0 gets it wrong. -Original Message- From: David Raymond Sent: Monday, November 20, 2017 11:03 AM To: 'SQLite mailing list' Subject: RE: [sqlite] SELECT result different after ANALYZE Confirming it's doing the same thing for me. Taking out the distinct

Re: [sqlite] SELECT result different after ANALYZE

p" for usage hints. sqlite> .timer off sqlite> .eqp off sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer) index|t1_1|t1|738|CREATE INDEX t1_1 on t1 (t2_id asc) table|t2|t2|1409|CREATE TABLE t2 (i

[sqlite] SELECT result different after ANALYZE

I am presenting a scenario where a SELECT produces a different result after running ANALYZE. To reproduce, download this database file (5.6MB, SHA1 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):

Re: [sqlite] SELECT INTO TEMP TABLE takes long

StmtJournal=0 11Goto 0 1 000 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Wednesday, May 31, 2017 5:59 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlit

Re: [sqlite] SELECT INTO TEMP TABLE takes long

Thanks guys for all the information. Now I know, how to proceed. Tom :) Am 31.05.2017 um 22:02 schrieb R Smith: On 2017/05/31 9:31 PM, Thomas Flemming wrote: Hi, maybe, hopefully, I missed something, its still about this database: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip

Re: [sqlite] SELECT INTO TEMP TABLE takes long

On 2017/05/31 9:31 PM, Thomas Flemming wrote: Hi, maybe, hopefully, I missed something, its still about this database: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip Copying just the ids from 12mio records ordered in a temp-table takes 60 seconds. There is a COLLATE NOCASE

Re: [sqlite] SELECT INTO TEMP TABLE takes long

On 31 May 2017, at 8:31pm, Thomas Flemming wrote: > Copying just the ids from 12mio records ordered in a temp-table takes 60 > seconds. There is a COLLATE NOCASE index on label. > > Is this normal or can this also be done faster? > > DROP TABLE IF EXISTS RowCursor; > CREATE

[sqlite] SELECT INTO TEMP TABLE takes long

Hi, maybe, hopefully, I missed something, its still about this database: http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip Copying just the ids from 12mio records ordered in a temp-table takes 60 seconds. There is a COLLATE NOCASE index on label. Is this normal or can this also be

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"? > That's joining two tables together. This is just because of the rtree, which is in Pois_bb (http://www.sqlite.org/rtree.html), has nothing to do with the second condition "styleid IN .." Am 30.05.2017 um 18:29 schrieb

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Thomas Flemming Tue, 30 May 2017 09:43:15 -0700 >> Try putting a "+" symbol before "styleid". Like this: >> >> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) > THATS IT !! :-))) > > 50ms with +, and 15000ms without the + > > How is that possible? Hello, best

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

If you scroll down in my previous reply I put the explain query plan outputs in with the queries. Guess I should have mentioned that. (Re-copied them below) It was using the index on StyleId, thinking that was going to be faster. What Dr Hipp suggested in adding the unary + operator does is

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> Try putting a "+" symbol before "styleid". Like this: > > AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) THATS IT !! :-))) 50ms with +, and 15000ms without the + How is that possible? Am 30.05.2017 um 17:36 schrieb Richard Hipp: On 5/27/17, Thomas

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

On 5/27/17, Thomas Flemming wrote: > Hi, > > I have a table Pois with points of interest (geogr. coordinate, label, > styleid) where I do regional querys using a rtree-index: > > SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862 >

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017 18:15 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow force it to go the way you want by using "cross

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

:15 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > force it to go the way you want by using "cross join" to force the > ordering of How would such "cross join" statemant look like? Am 30.05.2017 um 1

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

oun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Monday, May 29, 2017 9:28 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow Ok, here is a sample to try these queries: http://files.qvgps.com/0-tom-public/Geonames

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

; to force the ordering of the join. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Monday, May 29, 2017 9:28 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second cond

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

On May 30, 2017 10:07:45 AM EDT, Thomas Flemming wrote: >Style.Id doesn't need to be LONG, you're right. I changed it but it >doesn't >make a difference. >Pois.Id need to be LONG because the source for this column is really >containing 64-bit values Integers in SQLite are of

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Tuesday, May 30, 2017 8:08 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > > Do yo

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

to:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017 16:08 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > Do you know which SQLite version is being used by SQLite Expert

  1   2   3   4   5   6   7   8   9   10   >