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

SELECT prop_value FROM Table1 WHERE obj_id=10 AND prop_key='key1' AND (prop_tag='ios' OR prop_tag='*') ORDER BY prop_tag == 'ios' DESC LIMIT 1; You want to order by prop_tag == 'ios' in DESCENDING order. That is, the true (1) before the false (0). The default ascending sort will sort the

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

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

On Wednesday, 9 October, 2019 12:01, Jens Alfke said: >BETWEEN doesn't work well because it's inclusive, i.e. `BETWEEN 'foo' and >'fop'` doesn't work because it matches 'fop'. Coming up with the upper >end of a string prefix match is super annoying — `BETWEEN 'foo' and >'foo\xff' only works

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

Unable to reproduce. In particular: >SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? >tells me that it can and will use the (primary key) index on the name >column. will not use the index. I can make it use an index by doctoring the table data to make the index scan cheaper than a table

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

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

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, just a write. ie. This is wrong information. It

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 processing lines. So if you get rid of the "or rollback" then

Re: [sqlite] select within transaction

Roman Fleysher, on Friday, June 14, 2019 02:22 PM, wrote... > > Since ROLLBACK is not an error, I want SELECT to be executed only will update > actually happened (not rollback). Because of EXCLUSIVE, I want it to be in > one transaction and thus I need some indicator if SELECT was after

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

>Why does the "=" query fail and the "like" query work? There are no >wildcards involved. >The behavior is the same in Sqlite command line. There are no >wildcards involved. sqlite> create table songfiletable ( ...> songfile_id integer primary key, ...> dancename text ...> ); sqlite>

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 ?

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

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

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

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

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

Confirming it's doing the same thing for me. Taking out the distinct keyword will return a bunch of 1's, adding it in doesn't show them. Definitely something buggy here. D:\Temp>sqlite3 "analyze_problem - Copy.db" SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints.

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

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

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

> Do you know which SQLite version is being used by SQLite Expert > Professional 3.5? sqlite 3.10.0 I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same slow. 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

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

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: > Ok, here is a sample to try these queries: > > http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip > (825mb, 12 mio records) Just a few quick observations ... First, I would replace all column declarations like LONG PRIMARY

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_World_2017.zip (825mb, 12 mio records) Before I change my app-logic to do the styleid-query on the app-side, I would like to know, if there might be a chance to get this fast on the sqlite-side. very

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

Morning, > Does ANALYZE gather statistical data about rtree virtual tables? I seem to ANALYZE doesn't help. I'm busy preparing and uploading a sample-db, then it might be easier to figure that out. Tom Am 28.05.2017 um 11:01 schrieb Wolfgang Enzinger: Am Sat, 27 May 2017 19:20:00 -0400

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

Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp: > 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

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

Please post the output of the following command from the command-line shell: .fullschema --indent To capture the output of the above command, you can preceed it by ".once out.txt": .once out.txt .fullschema --indent To emphasize, use the command-line shell "sqlite3.exe" which you

Re: [sqlite] Select Statement returning incorrect information

...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, April 11, 2017 9:31 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Select Statement returning incorrect information On 12 Apr 2017, at 2:27am, Ron Barnes <rbar...@njdevils.net> wrote:

Re: [sqlite] Select Statement returning incorrect information

On 12 Apr 2017, at 2:27am, Ron Barnes wrote: > I needed to add the Cast parameter. Assuming you are actually storing integers, it might be better if you declared that column as integer in the first place. Then you wouldn’t need the CAST. However, well done for

Re: [sqlite] Select Statement returning incorrect information

I figured it out. I needed to add the Cast parameter. SELECTcategory, COUNT(*) AS Expr1 FROM(SELECT(CASE WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 1MB' WHEN CAST(VI_File_Len AS INTEGER) < 2048000 THEN 'Less Than 2MB' WHEN CAST(VI_File_Len AS

Re: [sqlite] Select Statement returning incorrect information

On 4/11/17, Ron Barnes wrote: > > I have been pulling my hair out trying to figure out where I went south. If > someone could, would you point out my mistake, please? What does this query show: SELECT DISTINCT typeof(VI_File_Len) FROM Volume_Information; And in

Re: [sqlite] Select older or not selected records

On 11 Nov 2016, at 12:59pm, Niall O'Reilly wrote: > IIUC, OP wishes to pick at random from the items which haven't yet been > used in the current cycle. That's different. To do that, you just need a flag 'seen' yes / no. When you enter a new quote set seen to 'no'.

Re: [sqlite] Select older or not selected records

On 11 Nov 2016, at 12:42, Simon Slavin wrote: No. When you use a quote you update the 'last used on' date for that table row. I think that's actually a "yes". IIUC, OP wishes to pick at random from the items which haven't yet been used in the current cycle. By simply using the 'last

Re: [sqlite] Select older or not selected records

On 11 Nov 2016, at 9:04am, Cecil Westerhof wrote: > Because you get always the same next quote after all quotes have been used. No. When you use a quote you update the 'last used on' date for that table row. Simon. ___

Re: [sqlite] Select older or not selected records

2016-11-11 9:49 GMT+01:00 Rowan Worth : > ORDER BY > CASE timestamp > WHEN NULL THEN -9223372036854775808 > ELSE abs(random())*timestamp > END > LIMIT 5? > > Completely untested, and the weighting function (ELSE clause) is almost > certainly terrible :P I think the

Re: [sqlite] Select older or not selected records

2016-11-11 9:56 GMT+01:00 Simon Slavin : > >> I want to select several quotes, with the following constraints: >> - As long there are quotes that are not selected, no record that was >> already selected should be selected. >> - How longer ago a record was selected, the bigger

Re: [sqlite] Select older or not selected records

On 11 Nov 2016, at 8:26am, Cecil Westerhof wrote: > I want to select several quotes, with the following constraints: > - As long there are quotes that are not selected, no record that was > already selected should be selected. > - How longer ago a record was selected,

Re: [sqlite] Select older or not selected records

2016-11-11 9:49 GMT+01:00 Rowan Worth : > ORDER BY > CASE timestamp > WHEN NULL THEN -9223372036854775808 > ELSE abs(random())*timestamp > END > LIMIT 5? > > Completely untested, and the weighting function (ELSE clause) is almost > certainly terrible :P I think the

Re: [sqlite] Select older or not selected records

ORDER BY CASE timestamp WHEN NULL THEN -9223372036854775808 ELSE abs(random())*timestamp END LIMIT 5? Completely untested, and the weighting function (ELSE clause) is almost certainly terrible :P I think the approach is ok, though I remember some recent threads suggesting the

  1   2   3   4   5   6   7   8   >