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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Shorty > Sent: Wednesday, 09 May, 2012 12:02 > To: sqlite-users@sqlite.org > 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','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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
