On 01/11/2018 12:48 PM, Shane Dev wrote:
Thanks, that works
Or, if you have a lot of data and an index on "value", this one might be
faster:
INSERT INTO max_value SELECT value FROM source_value ORDER BY value
DESC LIMIT 1;
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
