Hi Peter, Your solution quite simple and obvious in hindsight. Just to be clear - I am using the sqlite3 shell exclusively at the moment and only I post questions when I am stuck with a problem or observe behavior which appears not to be documented. I appreciate the time spent by yourself and other list members answering my questions.
On 11 January 2018 at 00:21, petern <peter.nichvolo...@gmail.com> wrote: > Functions and aggregates have to return a scalar value or NULL. Please > recall functions as a basic concept from early high school: > https://en.wikipedia.org/wiki/Function > > The query below will never return a NULL max(value) row from the > empty_table table: > > WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT > max(value)max_value FROM empty_table) WHERE max_value NOT NULL; > > Also, INSERTing zero rows is conditioned by having no rows in the source > SELECT like the following pattern: > > INSERT INTO ... SELECT ... FROM ... WHERE <the condition here filters all > rows> > > Shane. I encourage you experiment on your own in the shell of SQLite to > improve your intuition about SQL. Everything isn't a special case to be > learned by rote. There are are a few general patterns that, once > mastered, do explain what to expect most of the time. > > > > > > > > > > > > > > > > > On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev <devshan...@gmail.com> wrote: > > > Hi Ryan, > > > > Nice! I have never used IGNORE before. > > > > Would you agree the documentation is wrong for the case of SELECT max(X) > > FROM [an empty table or subquery]? > > > > max(X) > > > > The max() aggregate function returns the maximum value of all values in > the > > group. The maximum value is the value that would be returned last in an > > ORDER BY on the same column. Aggregate max() returns NULL if and only if > > there are no non-NULL values in the group. > > > > https://www.sqlite.org/lang_aggfunc.html > > > > > > On 10 January 2018 at 21:44, R Smith <ryansmit...@gmail.com> wrote: > > > > > Perhaps like this: > > > > > > -- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed > > > version 2.0.2.4. > > > -- Script Items: 4 Parameter Count: 0 > > > -- ============================================================ > > > ==================================== > > > > > > create table source_table(value); > > > > > > create table max_value(max_value NOT NULL); > > > > > > insert OR IGNORE into max_value select max(value) from source_table; > > > > > > select * from max_value; > > > > > > > > > -- Script Stats: Total Script Execution Time: 0d 00h 00m and > > > 00.031s > > > > > > > > > > > > On 2018/01/10 6:48 PM, Shane Dev wrote: > > > > > >> Hello, > > >> > > >> sqlite> create table source_table(value); > > >> sqlite> create table max_value(max_value); > > >> sqlite> insert into max_value select max(value) from source_table; > > >> sqlite> select * from table_max_value; > > >> max_value > > >> > > >> sqlite> > > >> > > >> > > >> How can the maximum value of column source_table.value be inserted > into > > >> max_value only if there are records in source_table? (If source_table > is > > >> empty, nothing should be inserted into max_value, not even a NULL) > > >> _______________________________________________ > > >> sqlite-users mailing list > > >> sqlite-users@mailinglists.sqlite.org > > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > >> > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
