I tried your query tonight, Igor, and it worked, not that I understand how...
the recursive query syntax is still a mystery to me and the many NOTs are a
challenge to interpret. Compared to the FTS solution, I observe some
preliminary differences in performance that are interesting.
The content
Igor, that is a most oblique and intriguing approach. I will try it out and
try to get my head around it in the next day or so.
Thanks,
Tom
Igor Tandetnik-2 wrote
> On 10/16/2014 12:03 PM, Tom Holden wrote:
>> I need a way to convert the text result to an expression that WHERE
>> evaluates as an
Igor Tandetnik-2 wrote
> Or alternatively, without a WHERE clause:
>
> update a set i = coalesce((select i from b where b.a = a.a), i);
What I have used similar to this is:
UPDATE a SET i = ifnull((select i from b where b.a = a.a), i);
Tom
--
View this message in context:
http://sqlite.106
"Mike Zang" ...
> In fact, the file is stock data and one file is for one stock, so they are
> all in the same format.
>
> Then, in most case, only one stock should be selected.
>
Then, from a 'disk' operating system and memory management perspective,
wouldn't one file per stock continue to be fa
"Peng Yu" ...
>> I think the penalty is in the extra JOIN required - 3 tables instead of
>> 2 -
>> with the speed advantage on the 'between' constraint being swamped by the
>> volume of intermediate rows.
>
> I don't quite understand why there are 3 tables with R-tree. Would you
> please show me w
"Eric Smith" ...
> I haven't used it myself, but I'm pretty sure this is what the R*tree
> module was designed for:
I have not used it either but was intrigued by your suggestion. Looking into
it, my sense was that it would be advantageous for a 2-dimension or more
search and I think this was bo
"Simon Slavin" ...
>
>> Here's how I have interpreted Simon's suggested chunky query:
>>
>> select * from A as A1, A as A2 where A1.name=A2.name and
>> A1.position != A2.position and
>> A2.chunk between A1.chunk - 1 and A1.chunk + 1 and
>> A2.position between A1.position - 10 and A1.position + 10 ;
"Simon Slavin" ...
>
> By the way, you might find that swapping the last two lines makes it
> faster:
>
>
>> select * from A as A1, A as A2 where A1.name=A2.name and
>> A2.position between A1.position - 10 and A1.position + 10 and
>> A1.position != A2.position ;
>
> But you might not: SQLite's op
"Simon Slavin" wrote ...
>
> On 21 Aug 2010, at 3:29am, Igor Tandetnik wrote:
>
>> ve3meo wrote:
>>> If the number of VM instructions is a good inverse indicator of
>>> efficiency,
>>> then the second query with the index appears to be the m
Without an index, EXPLAIN returns the following number of virtual machine
instructions for each query:
41 - original query:
select * from A as A1, A as A2 where A1.name=A2.name and
abs(A1.position - A2.position) <= 10 and
A1.position != A2.position ;
39 - original query re-ordered:
select * from
"Roger Binns" wrote in
message news:4c4b1ca7.1040...@rogerbinns.com...
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/24/2010 05:38 AM, ve3meo wrote:
>> I am a humble user of the command line implementation and of various
>> SQLite
>> manage
"Roger Binns" wrote in
message news:4c4a5bd5.5010...@rogerbinns.com...
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/23/2010 07:47 PM, ve3meo wrote:
>> Is it possible to store the results of a PRAGMA statement, especially
>> PRAGMA
>> d
Is it possible to store the results of a PRAGMA statement, especially PRAGMA
database_list in a SQLite temporary table using only SQLite commands? I have
tried every combination I can think of without success.
Tom
___
sqlite-users mailing list
sqli
I replicated your results. ORDERing the results seems to resolve the error
but clearly the GROUP BY is otherwise getting confused by the CASE.
This works:
select categ, count(1) from (select *, (case when a=0 then 0 else
cast((a + 25) / 50 + 0.5 as int) end) as categ from test order by a) a grou
"P Kishor" wrote in
message news:cdf6db501002270448s2d28ef4fm9a831fdfa7bf3...@mail.gmail.com...
> On Sat, Feb 27, 2010 at 6:43 AM, Peter Rodwell
> wrote:
>> I'm sure this question has been asked and answered a million times, but
>> I've not been able
>> to find the answer. Googling for it has
te should still do the
> right thing even if the ON is in the wrong place, and I'll fix that
> directly. Let me get Fossil running again first, though, please.
>
> Thanks for reporting the problem and for the analysis.
>
>>
>> Pavel
>>
>> On Wed, Feb 24
"ve3meo" wrote in
message news:hm47t5$5l...@dough.gmane.org...
>
> "Igor Tandetnik" wrote in
> message news:hm45gu$s5...@dough.gmane.org...
>> Mark Brand wrote:
>>> --Gives unexpected results
>>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(s
"Igor Tandetnik" wrote in message
news:hm45gu$s5...@dough.gmane.org...
> Mark Brand wrote:
>> --Gives unexpected results
>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate, x.rate) rate
>> FROM currency c1
>> CROSS JOIN currency c2
>> LEFT JOIN exchange x
>>ON x.cur1=c1.cur
>>AND x.
Thank you, thank you, Igor! This relates to the earlier thread I had started
titled "Speed regression after 3.6.17" where I was wrestling with a very
slow query because the query optimiser was selecting an inappropriate index,
one that was very chunky because the field was logical (0,1). This le
You're right! I arrived at a failure of a different kind because I did not
drop the Races table as per his script between adding the first record with
ID='20' and the second with ID=20. If Races table contains two records, the
first with ID='20' and the second with ID=20, his query fails, even t
That's great! I'm an end-user and cannot compile it to check myself. Thanks
to shane, drh, Hub Dog for the rapid fix. I will have to dig around to
figure out how/when that shows up in a release.
That leaves the question about the change of syntax between 3.5.4 and
3.6.17. In the earlier it was
I just discovered that attachments can be sent through this newsgroup so I
have attached a small database with which you should be able to reproduce
the problem. The one table in it has a field collated RMNOCASE. The
following query produces these results in three different versions of
sqlite:
Uhh, sounds like a search function of a text editor or a simple routine you
could write (or find) in your development language. I can't see the magic of
using sqlite.
Tom
"Michael Thomason" wrote
in message
news:3cbb39411001220619j1c6cc8f5x46e0aeb4d0b91...@mail.gmail.com...
Thank you. I lik
I originally experienced the problem using a couple of Windows sqlite
managers. It seems that ones using the latest few versions of sqlite have
the problem, variously reported as:
"Access violation at address x in module . Read of address
0005", where x is dependent on the ap
"Max Vlasov" wrote in
message news:7cb963ca1001130315o69235717n92393be027eef...@mail.gmail.com...
> >
>> After much playing about, I have determined that it is necessary to
>> violate
>> the guidelines on not using INDEXED BY and NOT INDEXED in an attempt to
>> optimise performance.
>
>
> Altho
"ve3meo" wrote in
message news:hiivpn$7f...@ger.gmane.org...
>
> "Simon Slavin" wrote in
> message
> news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org...
>>
>> The database structure has not changed. It is optimisation of a
>>
"Simon Slavin" wrote in
message news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org...
>
> The database structure has not changed. It is optimisation of a
> particular kind of query which seems to be working differently -- and not
> as well. Unfortunately, the example supplied is extremel
"Max Vlasov" wrote in
message news:7cb963ca1001120149u550da7fr56cfc0ed261a9...@mail.gmail.com...
> ... Are you sure you have
> identical tests for your comparison? I mean these are the same data sets
> with the same scheme?
Absolutely, errr, to the best of my knowledge. Same database, same quer
"D. Richard Hipp" wrote in
message news:4c2248d2-898d-41ec-81fc-bafdb726c...@hwaci.com...
>
> On Jan 11, 2010, at 9:28 PM, ve3meo wrote:
>> Oddly enough, by revising the query to explicitly
>> use an index, the two later releases are much faster...
>
> Did you
Max Vlasov writes:
>
> On Mon, Jan 11, 2010 at 2:33 AM, Tom Holden wrote:
>
> > I have been trying out a number of SQLite managers, one test being the
> > execution time for the same query on the same database on the same
computer.
> > The scattering of results was very surprising with a sprea
30 matches
Mail list logo
