Re: [sqlite] Backward cursor support?

No -- only navigable database support navigation (ie, hierarchical, network extended, etc). Relational Databases do not support scrollable cursors except through trickery and deception. You can save the primary key(s) values and re-issue the query with the ORDER BY reversed (row values make

[sqlite] Backward cursor support?

Hi, Does SQLite support backward cursor? Something like step_back()? If not, what would be a way to do it? Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] LIMIT doesn't return expected rows

#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 makes it work properly. neither NDEBUG nor SQLITE_DEBUG explicitly defined. Over to Richard ... > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Keith Medcalf > Sent: Tuesday, 11

Re: [sqlite] LIMIT doesn't return expected rows

By default, I do not see the query being flattened. Flattening it manually produces the same right results independant of something config.h does... SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC limit 3; 1 2 3 sqlite> select i.id from i, m where i.id=m.id order by flags

Re: [sqlite] LIMIT doesn't return expected rows

Well I can reproduce the wrong answer. If I compile the amalgamation code: with no config.h I get the same result as you, but with the following config.h, the result is correct (this is with the code from the head of trunk): #ifndef _CONFIG_H #define _CONFIG_H // Values of WINVER and

Re: [sqlite] LIMIT doesn't return expected rows

Following up: same for the Mac distribution of 3.14.2 command line tool. Using the pre-release snapshot of 3.15.0 from the main download page to build the sqlite3 3.15.0 on the Mac, I get the same answer: 1,2,3. SQLite version 3.15.0 2016-10-10 14:34:00 Enter ".help" for usage hints. Connected

Re: [sqlite] LIMIT doesn't return expected rows

Keith - using your example, I get the same result as Tobias: the second select produces 1,2,3. This is with the sqlite3.exe Windows command line tool for SQLite 3.14.2 downloaded from sqlite.org. Same sqlite_source_id() too. I’m not set up to build SQLite from source, so can’t easily test

Re: [sqlite] LIMIT doesn't return expected rows

SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .read \\test.sql select sqlite_source_id(); 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6 create

Re: [sqlite] Using nested SELECT with json_each()

Thanks much! (I’d forgotten about EXPLAIN QUERY PLAN — I had tried regular EXPLAIN, but the long list of virtual-machine instructions was too much for my brain.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Using nested SELECT with json_each()

Jens Alfke wrote: > SELECT DISTINCT user.name > FROM user, json_each(user.phone) > WHERE json_each.value LIKE '704-%'; 0|0|0|SCAN TABLE user 0|1|1|SCAN TABLE json_each VIRTUAL TABLE INDEX 1: 0|0|0|USE TEMP B-TREE FOR DISTINCT > For my purposes it seems cleaner to use a nested

Re: [sqlite] LIMIT doesn't return expected rows

On 10/11/2016 07:57 PM, Simon Slavin wrote: On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: Your problem is that although SQL accepts the clauses written in the order you wrote them in, the LIMIT clause is processed before the ORDER BY clause. Is that really true? I had

Re: [sqlite] LIMIT doesn't return expected rows

Am Dienstag, 11. Oktober 2016, 06:50:01 CEST schrieb Keith Medcalf: > This was fixed September 7. The fix appears in 3.14.2 and also on the > current 3.15.0. Does that mean that 3.14.2 is supposed to give the "6, 5, 7" result in the last query? I am asking as that's the version I am using

[sqlite] Ajqvue Version 1.10 Released

Ajqvue Version 1.10 Released The Ajqvue project is pleased to release v1.10 to the public. The release marks a complete code review and cleanup. Updated libraries have also been included with this release along with the Table Field Profiler plugin. There have been many fixes in this release that

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

http://www.gocomics.com/pearlsbeforeswine/2016/06/22 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Sunday, October 09, 2016 4:33 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Backward

[sqlite] Using nested SELECT with json_each()

I’m writing code to generate SQL queries that use json_each to test the contents of a JSON array. The docs give this as an example: SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%'; I’m not happy with this, as it requires the

Re: [sqlite] display also the average score of a flim

On 10/9/2016 9:01 AM, Roelof Wobben wrote: I have this sql script to display the top25 fiims based on the average score of a flim. I don't see where score figures in your query. You order films by the number of genres each is associated with. Is that what you meant? Now I wonder if it's

Re: [sqlite] Parallel access to read only in memory database

Jens Alfke wrote: On Oct 9, 2016, at 10:41 AM, Howard Chu wrote: As for code freshness, I've seen no compelling new features from 3.8.x onward that would improve performance so there's been no reason to update further. Perhaps, but there’s important new functionality in

Re: [sqlite] LIMIT doesn't return expected rows

On 11 Oct 2016, at 1:44pm, Eric Minbiole wrote: >> Your problem is that although SQL accepts the clauses written in the order >> you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was

Re: [sqlite] LIMIT doesn't return expected rows

This was fixed September 7. The fix appears in 3.14.2 and also on the current 3.15.0. https://www.sqlite.org/releaselog/3_14_2.html The ORDER BY LIMIT optimization is not valid unless the inner-most IN operator loop is actually used by the query plan. Ticket

Re: [sqlite] LIMIT doesn't return expected rows

> Your problem is that although SQL accepts the clauses written in the order > you wrote them in, the LIMIT clause is processed before the ORDER BY clause. > > Is that really true? I had always thought that the ORDER BY was processed first, though I admit I don't see anything authoritative either

Re: [sqlite] LIMIT doesn't return expected rows

On 11 Oct 2016, at 11:52am, Tobias Ellinghaus wrote: > Now I only want the first three values, 6, 5 and 7. However: > > sqlite> SELECT id FROM i WHERE id IN (SELECT id FROM m) ORDER BY flags DESC > LIMIT 0, 3; > 1 > 2 > 3 Your problem is that although SQL accepts the clauses

[sqlite] LIMIT doesn't return expected rows

Hello, first let me mention that I am new to this list so apologies if my question came up before. I couldn't find anything though, and in #sqlite on Freenode I was pointed here, so here I am. I am working on some code that creates a (potentially big) SQL query on the fly. My problem is, that

Re: [sqlite] Sqlite db version questions

On 11 Oct 2016, at 9:52am, Werner Kleiner wrote: > I am a little bit confused about the sqlite db version numbers. The version numbers you are seeing reported are not the version of the database file. All those databases are in the format for SQLite version 3.

[sqlite] Sqlite db version questions

Hello, I am a little bit confused about the sqlite db version numbers. 1. On Windows 7 I have created a new db with command sqlite3.exe test.db3 The sqlite3.exe is from date 2013-05-20 and the version number shows 3.7.17 2. Same computer but now I create a db with sqlite3.exe from

[sqlite] display also the average score of a flim

Hello, I have this sql script to display the top25 fiims based on the average score of a flim. SELECT films.* FROM films JOIN films_genres on films_genres.genre_id in (#{all_genres.join(' , ')}) WHERE films.id = films_genres.film_id and films.id !=2 AND count(films_genres.score) > 0 GROUP