select max(cast(userid as integer)) from maillist; NULL and any string will become 0 ...
--- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Shorty > Sent: Wednesday, 09 May, 2012 12:02 > To: [email protected] > Subject: Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL > But... WHERE <> '' returns value > > 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','[email protected]'); > INSERT INTO "maillist" VALUES(2,2,'Mike','[email protected]'); > INSERT INTO "maillist" VALUES(3,3,'Bill','[email protected]'); > INSERT INTO "maillist" VALUES(4,'','Steve','[email protected]'); > INSERT INTO "maillist" VALUES(5,NULL,NULL,NULL); > INSERT INTO "maillist" VALUES(6,' ','Dennis','[email protected]'); > > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
