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

[Shorty]

I've got a database that users can request to join the mailing list.   After 
they add their name and email address, I'll assign them a userID number.

The database looks like this:
recordID userID name email
1    1    John    j...@somewhere.com
2    2    Mike    m...@example.com
3    3    Bill    b...@example.com
4    null    Steve    st...@example.com

I want to find the higest userID so I can assign the next highest to Steve.

this does not work:
SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID IS NOT 
NULL
then highest_userID is returned with a value of NULL

this does not work:
SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID NOT NULL
then highest_userID is returned with a value of NULL

this does not work:
SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID NOTNULL
then highest_userID is returned with a value of NULL



this works:
SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID <> ''
then highest_userID is returned with a value of 3


I'm running php 5.2 that has sqlite 2.8.17
I looked up on the changelog
http://www.hwaci.com/sw/sqlite/changes.html
and it appears that NOT NULL is honored at version 2.0.1

Any idea why the NOT NULL won't work like <> '' ???

Thanks 

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users