Thanks, that works On 11 January 2018 at 06:40, Dan Kennedy <danielk1...@gmail.com> wrote:
> On 01/11/2018 03:41 AM, Shane Dev wrote: > >> Hi Dan, >> >> Your statement seems to insert a NULL into max_value >> > > So it does. How about this then: > > INSERT INTO max_value SELECT max FROM ( > SELECT max(value) AS max FROM source_table > ) WHERE EXISTS (SELECT 1 FROM source_table); > > Dan. > > > > > >> sqlite> delete from source_table; >> sqlite> delete from max_value; >> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE >> EXISTS (SELECT 1 FROM source_table); >> sqlite> select * from max_value; >> max_value >> >> sqlite> >> >> Erik Nelson's solution works - >> >> sqlite> delete from max_value; >> sqlite> delete from source_table; >> sqlite> insert into max_value select value from source_table order by >> value >> desc limit 1; >> sqlite> select * from max_value; >> sqlite> insert into source_table select 6; >> sqlite> insert into max_value select value from source_table order by >> value >> desc limit 1; >> sqlite> select * from max_value; >> max_value >> 6 >> sqlite> >> >> According to https://www.sqlite.org/lang_aggfunc.html - >> >> 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. >> >> despite that - >> >> sqlite> delete from source_table; >> sqlite> select * from source_table order by value; >> sqlite> select max(value) from source_table; >> max(value) >> >> sqlite> >> >> The behavior of SELECT max(X) from an empty table appears to contradict >> the >> documentation, or have I misunderstood something? >> >> >> >> >> On 10 January 2018 at 19:38, Dan Kennedy <danielk1...@gmail.com> wrote: >> >> On 01/10/2018 11: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) >>>> >>>> You could add a WHERE clause to your SELECT. >>> >>> INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT >>> 1 >>> FROM src); >>> >>> Or similar. >>> >>> _______________________________________________ >>> 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
