That doesn't appear to work for 2.8.17.
But using round() does work. NULL stays NULL and space (or any non-numeric string) becomes zero. Tried typeof() but it always returns numeric. SQLite version 2.8.17 Enter ".help" for instructions sqlite> CREATE TABLE "maillist"(recordID,userID,name,email); sqlite> INSERT INTO "maillist" VALUES(1,1,'John','j...@somehwere.com'); sqlite> INSERT INTO "maillist" VALUES(2,2,'Mike','mi...@example.com'); sqlite> INSERT INTO "maillist" VALUES(3,3,'Bill','b...@example.com'); sqlite> INSERT INTO "maillist" VALUES(4,'','Steve','st...@example.com'); sqlite> INSERT INTO "maillist" VALUES(5,NULL,NULL,NULL); sqlite> INSERT INTO "maillist" VALUES(6,' ','Dennis','de...@example.com'); sqlite> select max(cast(userID as integer)) as highest_userID from maillist; SQL error: near "as": syntax error sqlite> select max(round(userID)) from maillist; 3 sqlite> select recordID,round(userID) from maillist; 1|1 2|2 3|3 4|0 5| 6|0 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Petite Abeille [petite.abei...@gmail.com] Sent: Wednesday, May 09, 2012 1:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value On May 9, 2012, at 8:01 PM, Shorty wrote: > So, now the question is how do I write a statment to filter out strings so > the max() only evaluates the numeric values? SELECT max( cast( userID as integer ) ) AS highest_userID FROM maillist; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
