WHOOPS!!! I just re-created the database with a shell like Michael
describes and it WORKS!! I dumped my original database and now see the
problem, my data entry form that I used is making the blank userID field a
space instead of blank string or null.
Thanks a bunch Michael !!!!!
----------------------------------------------
So, now the question is how do I write a statment to filter out strings so
the max() only evaluates the numeric values? I know I could scrub the
database to fix the entries, but knowing how to ignore strings and nulls
would really help me in this case plus I can see it being valuable for other
cases too.
Here is the database version 3, I added Dennis that has a space for the
userID field:
CREATE TABLE "maillist"(recordID,userID,name,email);
INSERT INTO "maillist" VALUES(1,1,'John','j...@somehwere.com');
INSERT INTO "maillist" VALUES(2,2,'Mike','mi...@example.com');
INSERT INTO "maillist" VALUES(3,3,'Bill','b...@example.com');
INSERT INTO "maillist" VALUES(4,'','Steve','st...@example.com');
INSERT INTO "maillist" VALUES(5,NULL,NULL,NULL);
INSERT INTO "maillist" VALUES(6,' ','Dennis','de...@example.com');
I tried Michael's query on this database v3 and it returns a space:
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> '' and
userID IS NOT NULL;
Thanks
Shorty
from Michael D. Black
Hmmm...works for me....are you SURE you're using the correct database
after you made the changes?
..
sqlite> SELECT max(userID) AS highest_userID FROM maillist WHERE userID <>
'' and userID IS NOT NULL;
3
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
