Re: [sqlite] 3.18 Problem

On Sun, Apr 2, 2017 at 3:50 PM, David Burgess wrote: > I preferred the pre 3.18 behaviour. More flexible and fulfills a real > world requirement. The pre-3.18 behavior made it impossible to have the CHECK constraints checked. So I don't view that as "more flexible". If you

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 2017/04/03 10:51 AM, Hamish Allan wrote: Ah. My purpose is to determine "d for the most recent c with b=1", with "most recent" being "largest a". My query had been working for this, but I'd only been adding monotonically increasing values for "a" in my tests. I just tried doing otherwise

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Another option, if a is simple and a suitable index exists, is to simply get the max in a correlated subquery, so this should work faster even: SELECT c,d FROM x AS x1 WHERE b=1 AND a=(SELECT MAX(x2.a) FROM x AS x2 WHERE x2.b=x1.b AND x2.c=x1.c) This needs no grouping because the sub-query

Re: [sqlite] Get size of blob

On Sat, Apr 1, 2017 at 2:01 PM, Richard Hipp wrote: > On 4/1/17, Olivier Mascia wrote: > > > So if someone can confirm/infirm this assertion (about length(DBYTES) not > > involving needless I/O), I'd love to read you. > > Confirmed. Content is not read from

Re: [sqlite] SQLite Release 3.18.0

On 4/2/17, David Burgess wrote: > I tried optimize. > sqlite> pragma main.optimize; > Error: database schema has changed > I think that means you ran "PRAGMA optimize" as the very first command on a freshly opened database connection. This should be a no-op. Read the go-bys

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

On Sun, 2 Apr 2017 09:40:36 +0200 Clemens Ladisch wrote: > > Why not use vis(3) instead? > > Because vis() is a nonstandard function that is not available > everywhere, "everywhere" is a high standard, but vis is freely available and included or packaged with almost

Re: [sqlite] 3.18 Problem

On Sun, 2 Apr 2017 23:50:01 +1000 David Burgess wrote: > The rules for the format of these numbers has changed over time and > the schema has changed with the rules of the time. PRAGMA > integrity_check now complains about some rows not passing the > constraint. No one

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

This bit me... I fat-fingered a command and deleted my database. I had a backup dump taken earlier in the day. Go to restore it and all of a sudden this error starts cropping up. I'm stuck. I need to get this database back online, but even with 4000 limit I am losing INSERTs. I get segfaults when

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

select max(a), c, d from x where b == 1 group by c order by 1 desc; index on (b, c, a) So, you are grouping by c where b=1, and within each grouping returning the grouping (c), the maximum value of (a) for that grouping, and (d) from one of perhaps multiple rows that contain the

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Thanks, Keith. I think I need the sub-select because I don't just want the (d) for the max(a) for the grouping of (c), but for the combination of the grouping (c) and the given value (b). On 3 April 2017 at 14:32, Keith Medcalf wrote: > select max(a), c, d > from x >

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

The correlated subquery is fine if your SQL must be portable, but will return n rows if the a value is duplicated (n times) within a group. The bare field select only works in SQLite and is probably faster, with the caveat that the row the bare field values are taken from is "random". Since you

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

This second one also has the advantage that I understand what's being expressed!  This works perfectly, thank you! (and thank you to all others that replied). I'm not sure I understand the indexing, though. The query plan without indexes is: 0|0|0|SCAN TABLE x AS x1 0|0|0|EXECUTE CORRELATED

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

Cousin Stanley wrote: >> regarding your sqlrpt program >> >> Can the floating point numbers be formatted >> for example ah la %8.4f James K. Lowden wrote: > Cousin Stanley, > > Indeed you now can. > > I added a -p option, documented in the man page. > > You supply the

[sqlite] Search semantics with a virtual table?

How do I convince SQLITE to give me SEARCH semantics over a virtual table instead of SCAN semantics? e.g. I have: explain query plan SELECT * FROM vtable WHERE value = 12345 0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1: And I'm returning in xBestIndex: pIdxInfo->idxNum = 1;

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Thanks, Hick. I now understand that it's undefined which value is selected from the bare column. To (hopefully) clarify my purpose: I'm adding a row (with incrementing "a") each time "d" is changed for a given combination of "b" and "c". But the way I want to query is: for each "c" with a given

Re: [sqlite] Search semantics with a virtual table?

The SQLITE_INDEX_SCAN_UNIQUE flag is a hint for the query planner. It does not affect query execution mechanics. You should be returning TRUE from xEOF after the first call to your xNext function (provided indeed that there is only 1 row that matches the value). Or you need to add a LIMIT 1

Re: [sqlite] Continuous recovery of journal

Hi All, I am new to this mailing list. Is anyone working with Visual Studio 2015 or belter and using reportviewer? If yes, what was the procedure? I'm having the devils of a time trying to generate reports using SQLite. Would anyone be able to provide a TuT or maybe a link to one tha can

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Ah. My purpose is to determine "d for the most recent c with b=1", with "most recent" being "largest a". My query had been working for this, but I'd only been adding monotonically increasing values for "a" in my tests. I just tried doing otherwise and found that I had been relying on an

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I am not sure I correctly understand what you want. This is the value of d associated with a randomly chosen record from the group of records having the highest value of a tha also fulfills b == 1; SELECT MAX(a),d FROM x WHERE b=1; If you want the value of d within each group of records

Re: [sqlite] Reporting Solutions that work with SQLite / VB 2015?

On Mon, 03 Apr 2017 06:26:35 -0700 Cousin Stanley wrote: > e.g. lines with . == My mistake, sorry. A little git hiccup. You found the correct fix. And the repository now holds a correct version. --jkl