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
