Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value

[Shorty]

I ran this through the shell:

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');
SELECT max( cast( userID as integer ) ) AS highest_userID FROM maillist;

I got: "Error in query: near "as": syntax error"

I'm running php 5.2 that has sqlite 2.8.17 and just looked up the change log 
and it appears that CAST might not be available for sqlite2:
http://www.hwaci.com/sw/sqlite/changes.html
Or did I screw up the query another way?

Thanks
Shorty



from Petite
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