[sqlite] Search query alternatives.

Hi all, first off I must start with an apology. I know I'm sort of doing the wrong thing here as this question is NOT related to sqlite. It is a general SQL question but I ask it here because I have great respect for the answers and discussions I have seen on this forum over many years. I rarely

Re: [sqlite] Search query alternatives.

e operation to remove the duplicate records from the result set? I'm assuming. I'll give that a try. On 16 October 2014 10:05, RSmith <rsm...@rsweb.co.za> wrote: > > On 2014/10/16 00:05, Michael Falconer wrote: > >> Hi all, >> >> first off I must start

Re: [sqlite] Search query alternatives.

2014 10:30, Michael Falconer <michael.j.falco...@gmail.com> wrote: > Yes, I agree with the general sentiment. It is not exactly the design I > would have chosen either, but it is what exists. Design change is occurring > but for the moment we are stuck with the current schema. &g

Re: [sqlite] Search query alternatives.

t; both queries several times, ditch the longest and shortest times, then take > the mean or average times and do the comparison that way. > > On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer < > michael.j.falco...@gmail.com> wrote: > > > Igor, > > > > Nice one.

Re: [sqlite] Search query alternatives.

Thanks Eduardo, a most interesting link. On 17 October 2014 05:41, Eduardo Morras <emorr...@yahoo.es> wrote: > On Thu, 16 Oct 2014 09:05:51 +1100 > Michael Falconer <michael.j.falco...@gmail.com> wrote: > > > Hi all, > > > > first off I must start wi

Re: [sqlite] Search query alternatives.

:51 +1100 > Michael Falconer <michael.j.falco...@gmail.com> wrote: > > > we just wonder if there is a better way to perform this search in > > SQL. Is there a general technique which is superior either in speed, > > efficiency or load bearing contexts? > > The simple

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

Gerald, thanks for this, it looks most interesting on both levels. i.e. The data release and your project work. Thank you for the information and your project. On 2 November 2015 at 01:49, Gerald Bauer wrote: > Hello, > > I've started a new project, that is, /factbook.sql [1] that offers >

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

Outstanding! it is kudos which is so richly deserved, and IMHO, an example of REAL integrity earned. Over the years I have watched as SQLite developed, learned much from this list, and was impressed by Dr Hipps hands-on leadership in directing development in what he and the team deemed an

[sqlite] json_group_array

I just love this list and the contained discussions. Doffing my cap to Keith and his succinct and very useful summary of the subtle differences regarding aggregate type functions. Especially like the nested stuff, which I must admit had not really occurred to me. Nice stuff Keith and thanks for

[sqlite] Performance comparison between SQLite and SQL Server?

Good thread, which absolutely nails the point 'dev decisions for app cases' make a developers world go round. I personally couldn't think of a greater waste of time than a benchmark comparison between client server rdbms's and sqlite. Do what benefits your case most. The above from Jim pretty

[sqlite] Get count of unique values?

Both Igor's and Simon's solutions work on my test system. The only additional comment is that Simon's works in Sqlite as expected. However running the same on PostgreSQL bombs with an error complaining about an sub-query with no name! Interesting? Perhaps watch out for portability with that one.

Re: [sqlite] Best way to implement Find commands

Leading on from what Keith suggests above re: 'and join' can we simplify as: Findfirst: SELECT * FROM some_table WHERE original_expression AND new_expression LIMIT 1; What the LIMIT 1 will return depends on what index/primary key is in effect, sort of hoping for rowid here :-) This appears

Re: [sqlite] Best way to implement Find commands

013 at 12:47 PM, Michael Falconer < michael.j.falco...@gmail.com> wrote: > Leading on from what Keith suggests above re: 'and join' can we simplify > as: > > Findfirst: > > SELECT * FROM some_table WHERE original_expression AND new_expression > LIMIT 1; > > What the

Re: [sqlite] Best way to implement Find commands

Hi Vance, ...yes it seems reasonable to expect the same resulting order if your subsequent (temp creating) query is identical to that which was run previously. However I can understand, from a theoretical and experience point of view, why you might feel uneasy about relying on a rowid as the

Re: [sqlite] 64-bit SQLite3.exe

Smith <rsm...@rsweb.co.za> wrote: > > On 2016/08/10 11:35 PM, Michael Falconer wrote: > >> ...// Ryan is very >> right about the relative ease of such exercises but IMHO wrong that it >> suits all, or even most cases. >> > > Just to be clear - if my post

Re: [sqlite] Exec vs Prepare, step, finalize.

Thanks all, must admit to being around db's for years but I never did get my head around the whole injection thing, sad but true. Keith summed it up in usual succinct fashion which when read by one old hack cause much reddening of the facial features. Bugger, says I, that speaks my language and

Re: [sqlite] Exec vs Prepare, step, finalize.

...as for Stephen, Mr Beal you need to get out more LOL! Little Johnny Tables indeed. Rub it in, why not? LOL On 12 August 2016 at 09:38, Michael Falconer <michael.j.falco...@gmail.com> wrote: > Thanks all, > > must admit to being around db's for years but I never did get m

Re: [sqlite] 64-bit SQLite3.exe

Great discussion, if somewhat amusing (with a hint of deja vu) for an old hack like myself. I remember discussions like this back in CP/M days when it was a move rom 8 to 16bit. Oh, and it all happened again when 32bit came along and yes now we are into 64bit and it carries on. Simple really,

[sqlite] Exec vs Prepare, step, finalize.

Hi all, just seeking some opinions, and perhaps some dev indications about deprecation, in relation to the sqlite3_exec facility. I kind of like the callback functionality in certain cases as it is convenient in some circumstances where the sql injection problem is not an issue. Ok I say it is

Re: [sqlite] Exec vs Prepare, step, finalize.

> > On Aug 10, 2016, at 9:21 PM, Michael Falconer < > michael.j.falco...@gmail.com> wrote: > > > Hi all, > > > > just seeking some opinions, and perhaps some dev indications about > > deprecation, in relation to the sqlite3_exec facility. I kin

Re: [sqlite] Simple web query tool

> > Running the sqlite3 command-line shell via cgi works way better than you > may expect. > ​Yay verily, and that is really not doing a great tool justice. I've done a lot of similar things to what Lindsay outlines above both with web and application targets, Often these procedures are set up as

Re: [sqlite] SQLite output mode

Just from some quick command line tool playing around, a dirt quick fix presented itself. .mode csv followed by .mode column (following the .mode ascii operation) seems to return the .column mode to normal. This on SQLite 3.16.2 on Linux command line. I'm not suggesting this as a fix as Dr Hipp

Re: [sqlite] Possible bug when creating a calculated index

o index a string literal?), but > still, the behavior deviates from what's expected, the expected > behavior being that we should get a calculated index. > > Best, > Ersin > > On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer > <michael.j.falco...@gmail.com> wrote: > > S

Re: [sqlite] Possible bug when creating a calculated index

Thank you Simon, I do see the inconsistency and thanks for those examples. I had answered previously before I saw your explanation and I now see why there is concern. It certainly appears to be inconsistent given such use cases. On 27 January 2017 at 10:26, Michael Falconer <michael.j.fa

Re: [sqlite] Possible bug when creating a calculated index

Simon, as I see it there is no problem here. Explicit quoting regardless, the column does not exist and an error is returned, isn't this the expected outcome? In the DRH quoted section a reason is presented as to why no error is returned due to a built in default action. This may or may not be a

Re: [sqlite] thousand separator for printing large numbers

Congrats to all contributors to this thread. Robust discussions like this make this my absolute favourite list. For the record I like the OP's suggestion which was more about the features of the printf() function than anything else. Everybody wins though, because of the great discussion and the

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

Nice Keith, and very topical as well as being informative. Note a couple of things that got my curiosity chip activating: *subquery in result set* test produces interesting outcome with the 64 bit version bucking the trend. Any ideas there? Also it would appear *select* clauses demonstrate an

Re: [sqlite] 64-bit SQLite3.exe

Yes Richard, in my experience your quoted time benefits are very much on target. I have rewritten Excel/Access (VBA) which I originally developed (and was quite proud of). Re-writes using C/C++ and SQLite indeed delivered the performance gains you experienced and relieved me of the pain

Re: [sqlite] importing csv

Alan, if you are new to sqlite3 then let me give you a little heads up on something that tripped me up a couple of times in early days. I am assuming you are the command line sqlite3 interface which will require you to set the separator for data imports. .separator , ...is likely what you want

Re: [sqlite] SQLite3 Tutorial error

UmSnobol? Nope absolutely deny all knowledge. Well.actually, didn't cut any code in it but did hear about Spitbol. No I kid you not, sort of a super Snobol I was led to believe. I was cutting Cobol at the time, eons ago. :-) A colleague told me about it and I took a look but not a close

Re: [sqlite] Why isn't my time formatting working?

UmI'm one of those sad old hacks who doesn't store dates at all. Just Years, Months, and Days. You can do just about anything with them stored that way, and in sqlite it seems to matter little whether they are string or numeric columns. Dates are a rubbery concept not well suited to db storage

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

Just amusing, and found while looking for above. Researchers have disclosed a vulnerability in *Android's* SQLite that can > leak sensitive information without an application having adequate > privileges. > ​So when did DRH sell out to Android? :-) From an old (2012) blog report ​HERE

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

These may enlighten a little..at least it appears to be related? http://www.cvedetails.com/cve/CVE-2015-6607/ IBM report On 17 April 2017 at 22:09, Simon Slavin wrote: > > On 17 Apr 2017, at 10:35am,

Re: [sqlite] Regarding CoC

Oh dear, it's all gone...while my devout atheism is generally pleased my somewhat annoying 'free will, free speech ' ethic has rust on it! Richard, it's your joint and it's such a good place, friendly and mostly respectful. My atheism was NOT offended in any way by all that God speak and I do

Re: [sqlite] Regarding CoC

> > I found code of conduct in documentation and I was wondering if it were > true. Checking the version history it appears to have been added on > 2018-02-22. > Sure that publishing date wasn't 2018-04-01? On Wed, 24 Oct 2018 at 08:02, Stefan Evert wrote: > > > On 23 Oct 2018, at 07:04, Paul

Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

As Simon points out there is no SQL solution to your issue. Some sort of external utility processing with things like awk, sed or even cut may assist or for a quick and dirty method you could set the sqlite3 command line utility .separator value to a blank string which may (or may not) provide a

Re: [sqlite] How many digits do you need ?

Nice one Simon, as a resident Australian (and a lover of levity) I liked your link, which led me on to the data source. I took a pair of those 15 digit coordinates and pumped them into good old Google maps but sadly I was unable to zoom in far enough to see any chlorine atoms. :-(. Shame that.

Re: [sqlite] OT!!!!! Understanding the WITH clause

SELECT peace FROM disaster WHERE disaster.cause = 'Windows'; Returns: *nix On Thu, 20 Jun 2019 at 14:26, Sam Carleton wrote: > James, > > That is a really, really interesting quote. it really got me thinking > about peace and the definition of it. It got me thinking so much I wanted > to

Re: [sqlite] 18 minutes 41 seconds

Great work but pretty much what we have come to expect from DRH and the SQLite team. As we approach the end of yet another year ( and indeed decade ) can I indulge the list in a simple congratulations to all involved and to the outstanding support on offer when a member of this email list. SQLite

Re: [sqlite] 18 minutes 41 seconds

e the fact that there was, in fact, no year 0. Obviously the character(s) responsible for dates etc were NOT C programmers! On Tue, 31 Dec 2019 at 14:45, Richard Damon wrote: > On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote: > > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf > wrote: >

Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

Ahthe inevitable becomes reality. I knew when this stuff surfaced a few years back that it was not going to go away, and this outcome was just a matter of time. Not a great a great shock as a life in programming has seen so much change. I don't need to Fossil, because I am one. Also a bit of a