[sqlite] Can this be done in SQLite?

Brilliant - thanks.Though I still do not understand my errordistinct(t.LastName || ', ' || t.FirstName),Seems to me that I am passing a single argument in parentheses to distinct > To: sqlite-users at mailinglists.sqlite.org > From: rsmith at rsweb.co.za > Date: Fri, 9 Oct 2015 00:38:10

[sqlite] Can this be done in SQLite?

On 8 Oct 2015, at 10:47pm, K. P. wrote: > Am using SQLiteStudio - which I really like - though I have wondered if it > passes all its knowledge around errors onto the user... For such experimentation I recommend the SQLite command line tool. It's the thinnest practical shell around the raw

[sqlite] Can this be done in SQLite?

On 8 Oct 2015, at 9:54pm, K. P. wrote: > Thanks for that.I'd need something along the lines of > > group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName, > > > which in itself does not seem to be supported. Why not ? What is the complete SELECT command ? What happens when

[sqlite] Can this be done in SQLite?

I tried this, of course, before asking, but: group_concat(distinct(t.LastName || ', ' || t.FirstName), '; ') As Teachers, gives [15:19:32] Error while executing SQL query on database 'test': DISTINCT aggregates must have exactly one argument > To: sqlite-users at mailinglists.sqlite.org > From:

[sqlite] version 3.9.0 doc errors

*** Correction *** On 2015-10-08 10:03 PM, R.Smith wrote: > > To clarify, when used as an adverb to modify a verb, you may well add > the s - such as saying "I'm moving backwards" or "It's a forwards > marching army."//... "Marching" is of course an adverb here, not a verb. A more correct

[sqlite] version 3.9.0 doc errors

On 2015-10-08 08:58 PM, Donald Griggs wrote: > Regarding: >2nd paragraph - The New Version Numbering System (auxiliary letter): > "The second number Y is incremented for any change that breaks forward*s* > compatibility..." > > Not that it matters much, but in general it seems that adding

[sqlite] Can this be done in SQLite?

Almost there - just one issue left: I now receive a list of students per class - perfect. I also do a group_concat on the teachers (as a class could be taught by more than one) and so I I get the same teacher repeated a number of times at times. Any way I can apply a 'distinct' here? > To:

[sqlite] Can this be done in SQLite?

Am using SQLiteStudio - which I really like - though I have wondered if it passes all its knowledge around errors onto the user... > To: sqlite-users at mailinglists.sqlite.org > From: rsmith at rsweb.co.za > Date: Thu, 8 Oct 2015 23:34:05 +0200 > Subject: Re: [sqlite] Can this be done in

[sqlite] Can this be done in SQLite?

It does indeed work - had to make myself a simpler query than the real life one to see this. I probably had some other syntax error that I mistook for it... Thanks! > To: sqlite-users at mailinglists.sqlite.org > From: rsmith at rsweb.co.za > Date: Thu, 8 Oct 2015 23:19:53 +0200 > Subject: Re:

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

On 10/8/15, Darren Duncan wrote: > > 2. If two successive versions have an overlapping but not equal API and > file format, meaning that a subset of data files but not all of such readable > or > writeable by one version is readable and writeable by the other, or that a > subset of code but not

[sqlite] Can this be done in SQLite?

Thanks for that.I'd need something along the lines of group_concat(c.LastName || ', ' || c.FirstName, ';') as ClientName, which in itself does not seem to be supported. c.LastName || ', ' || c.FirstName, ';') as FullName, group_concat(FullName, ';') as ClientName, also not :( Any

[sqlite] version 3.9.0 doc errors

More draft doc errors: https://www.sqlite.org/draft/versionnumbers.html 1st paragraph - SQLite Version Numbers (typo for "through"): "There are two strategies for version numbers in SQLite. The historical system, in use from the first release on 2000-08-17 *though* version 3.8.11.1 on..."

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 2015-10-08 06:21 PM, Richard Hipp wrote: > It all really boils down to this: What is the difference between a > "major" and a "minor" change? > Agreed, and the decision between whether an item is major or minor is always going to be a blurred line. It is not important for the issue at

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

On 2015-10-08 6:03 PM, Richard Hipp wrote: > On 10/8/15, Darren Duncan wrote: >> >> 2. If two successive versions have an overlapping but not equal API and >> file format, meaning that a subset of data files but not all of such >> readable or >> writeable by one version is readable and

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

Richard, I agree with your proposal herein stated, at least as I understand it. I would propose that with a W.X.Y semantic version scheme, which I think is what you said, the parts mean essentially [disjoint.overlapping.equal], by which I mean: 1. If two successive versions have a disjoint

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 8 October 2015 at 15:38, Richard Hipp wrote: > Several users have proposed that SQLite adopt a new version numbering > scheme. The proposed change is currently uploaded on the "draft" > website: > > https://www.sqlite.org/draft/versionnumbers.html >

[sqlite] Problem with sqlite3_db_filename

Ignore this as I know what the problem was. I was passing a pointer to the Unicode string, but should be pointer to 8bit ASCII string. RBS On Thu, Oct 8, 2015 at 9:58 AM, Bart Smissaert wrote: > OK, thanks, at least I know that the function works fine then in > sqlite3.dll. > Problem must be

[sqlite] Can this be done in SQLite?

You should be able to concatenate the first and last name in a subquery and then use group_concat to output the single column you describe. For example: select group_concat(clientname, ';') from (select FirstName || ',' || LastName from yourtable); On Thu, Oct 8, 2015 at 4:54 PM, K. P. wrote:

[sqlite] Can this be done in SQLite?

On 8 Oct 2015, at 4:59pm, K. P. wrote: > Given the following tables, is it possible to extract rows of class data > along with all participating student names concatenated in one column in a > single SQL query? See the 'group_concat()' function:

[sqlite] Explain query plan bug: overflow of subquery_id

As for version 3.8.6, when executing: sqlite> explain query plan with a as ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

> -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: donderdag 8 oktober 2015 16:36 > To: General Discussion of SQLite Database users at mailinglists.sqlite.org> >

[sqlite] [sqlite-dev] SQLite version 3.8.12 enters testing

All the extensions have to be explicitly enabled by compile-time flags. This is the case for FTS3/4, which has been included in the amalgamation for several years. The RTree and also the new JSON extension are included as well. It only seems logical that FTS5 should be included too as it is part

[sqlite] Can this be done in SQLite?

Given the following tables, is it possible to extract rows of class data along with all participating student names concatenated in one column in a single SQL query? Classes---IDDateSubject ParticipantsIDClassIDStudentID StudentsIDName As always, thanks for

[sqlite] [sqlite-dev] SQLite version 3.8.12 enters testing

Would it be possible to include FTS5 in the amalgamation? Thanks, Filip Navara On Wed, Oct 7, 2015 at 4:42 PM, Richard Hipp wrote: > The release checklist for version 3.8.12 > (https://www.sqlite.org/checklists/3081200/index) is now active. The > 3.8.12 release will occur when the checklist

[sqlite] Problem with sqlite3_db_filename

On 10/08/2015 03:51 AM, Bart Smissaert wrote: > As I understand it this should produce a filepointer to the filepath of the > attached database, given the database handle of file the other database was > attached to and the database name of the attached database. I checked all > the return values

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

On 8 Oct 2015, at 2:38pm, Richard Hipp wrote: > If accepted, the new policy will cause the next release to be 3.9.0 > instead of 3.8.12. And the second number in the version will be > increased much more aggressively in future releases. I approve of this particular release changing the Y

[sqlite] version 3.9.0 doc errors

Regarding: 2nd paragraph - The New Version Numbering System (auxiliary letter): "The second number Y is incremented for any change that breaks forward*s* compatibility..." Not that it matters much, but in general it seems that adding the "s" to backward and forward is more often done in British

[sqlite] [sqlite-dev] SQLite version 3.8.12 enters testing

I expect users running SQLite on embedded devices would be thrilled... -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Filip Navara Gesendet: Donnerstag, 08. Oktober 2015 15:55 An:

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 10/8/15, Scott Robison wrote: > > 3.1.0 is released > 3.1.1 fixes a bug in 3.1 > 3.2.0 is released with new features. > 3.2.1 fixes a bug originally introduced in 3.1.0 > > Some people are going to expect that bug fix to be back ported into the 3.1 > branch and have a 3.1.2 release cut from

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

I'd add the disclaimer to the page that once 3.2.0 is built, 3.1.x becomes inert and any fixes pertaining to that particular release belongs to the 3.2.* release. "Lawyer speak", I guess one could coin it. What the "Effect Definition" is of what Major and Minor is, I think Richard draft page has

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 10/8/15, Stephen Chrzanowski wrote: > > What the minor and build versions mean is subject to change and offer an > air of confusion. With a 3.{Date} version, you get a concrete "This is the > day the product was built with all features and known bug fixes". And it'd > be just as easy for

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

To look at it another way, in version X.Y.Z, it has always been the case and will remain the case that Y is incremented for major changes and Z is incremented for minor changes. The proposed policy change is to alter the definition of "major" and "minor" such that *anything* other than a simple

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

I'm indifferent, to be quite honest, how the version numbers work and what I download, so long that there is a difference and things become more solid with both enhancements and fixes.. Set the file name and revisions to a date, for all that it matters to me, or just label is as

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 10/8/15, Marc L. Allen wrote: > However, CTE is a functionality enhancement that, I believe, does not impact > the ability of previous SQLite versions to work with the database. The > thing is, I don't believe CTE is simply a "performance enhancement." To me, > a "performance enhancement"

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On 10/8/2015 11:51 AM, Marc L. Allen wrote: > However, CTE is a functionality enhancement that, I believe, does not impact > the ability of previous SQLite versions to work with the database. One could create a view, or a trigger, that uses CTE query. That's not backward-compatible. Basically,

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

Ah. Of course. Thanks for waking me up.. both you and Igor. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, October 08, 2015 12:12 PM To: General Discussion of

[sqlite] SQLite version 3.8.12 enters testing

On Wed, Oct 7, 2015 at 9:42 PM, Richard Hipp wrote: > On 10/7/15, Zsb?n Ambrus wrote: > New documentation covering indexes on expressions has been added. > Please let me know if you think more is needed. Thanks, that's much better. The "http://sqlite.org/draft/expridx.html; documentation tells

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On Thu, Oct 8, 2015 at 10:53 AM, Richard Hipp wrote: > On 10/8/15, Scott Robison wrote: > > > > 3.1.0 is released > > 3.1.1 fixes a bug in 3.1 > > 3.2.0 is released with new features. > > 3.2.1 fixes a bug originally introduced in 3.1.0 > > > > Some people are going to expect that bug fix to be

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

More thoughts... My quandary is with compatibility in regards to file formats only. If you extend it to API, the CTE would require a Y change. Code that uses SQLite and runs with Y may not operate with Y-1 API. And, on a side note... the new versioning scheme also means that changes, such

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

Just my $0.02... In the proposed new versioning system: Partial Indexes is clearly something that requires Y to be incremented as Y-1 won't be able to handle a database with partial indexes. However, CTE is a functionality enhancement that, I believe, does not impact the ability of previous

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

On Thu, Oct 8, 2015 at 10:21 AM, Richard Hipp wrote: > To look at it another way, in version X.Y.Z, it has always been the > case and will remain the case that Y is incremented for major changes > and Z is incremented for minor changes. The proposed policy change is > to alter the definition of

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

On Thu, Oct 8, 2015 at 8:36 AM, Simon Slavin wrote: > > On 8 Oct 2015, at 2:38pm, Richard Hipp wrote: > > > If accepted, the new policy will cause the next release to be 3.9.0 > > instead of 3.8.12. And the second number in the version will be > > increased much more aggressively in future

[sqlite] Strange behaviour of IN/NOT IN subqueries?

Indeed you did reproduce in that you selected all of the 1 entries you inserted into table item and inadvertently used the field name from the table in the subquery in the NOT IN case ;) A subquery "select itemcode from tmp_salesitm", is allowed to refer to fields from the outer "select * from

[sqlite] Problem with sqlite3_db_filename

OK, thanks, at least I know that the function works fine then in sqlite3.dll. Problem must be on my side then. This is the code in the Std_Call dll: SQLITE3_STDCALL_API const char * __stdcall sqlite3_stdcall_db_filename(sqlite3 *pDb, const char *zDbName) { return sqlite3_db_filename(pDb,

[sqlite] SQLite version 3.8.12 enters testing

On Wed, Oct 7, 2015 at 11:01 PM, R.Smith wrote: > [...] (Brilliant release btw, thanks) > +1 > [...] This definitely feels like a 3.9 release rather than a 3.8.12. I > mean it introduces functionality that a 3.8.11 engine won't be able to > comprehend - and not just one obscure item either. >

[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested

Several users have proposed that SQLite adopt a new version numbering scheme. The proposed change is currently uploaded on the "draft" website: https://www.sqlite.org/draft/versionnumbers.html https://www.sqlite.org/draft/releaselog/3_9_0.html https://www.sqlite.org/draft/ If

[sqlite] SQLite version 3.8.12 enters testing

On 10/8/15, Zsb?n Ambrus wrote: > > There is one apparent inconsistency I was wondering about. For a > partial index, the WHERE clause of the index cannot contain functions; > whereas for an index on an expression, the expression can contain > deterministic functions. What is the explanation of

[sqlite] version 3.8.12 doc errors

Documentation errors: https://www.sqlite.org/draft/vtab.html#epovtab In the 1.1.2 Eponymous virtual tables section (auxiliary word): "Note that SQLite versions prior to 3.8.12 did not check the xCreate method for NULL *before to* invoking it..." ---

[sqlite] SQLite version 3.8.12 enters testing

Some notes on 3.8.12: (Brilliant release btw, thanks) I've been following the thread about the version numbering, and at first thought the OP was a little over-enthusiastic about increasing version numbers prematurely, but then after reading the release notes, I have to agree to some extent -