I now realize that my data entry form was making the Steve entry a blank
string, and that is why the <> '' worked.
Original database
recordID userID name email
1 1 John j...@somewhere.com
2 2 Mike m...@example.com
3 3 Bill b...@example.com
4 '' Steve st...@example.com
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> '' AND
userID IS NOT NULL
SELECT max(userID) AS highest_userID FROM maillist WHERE (userID <> '') AND
(userID IS NOT NULL)
SELECT max(userID) AS highest_userID FROM maillist WHERE userID NOT IN ('',
NULL)
all of these return 3, which is what I am trying to get.
----- HERE IS THE THING I AM
AFTER -----------------------------------------------
I am still interested in finding the max value of userID if that column does
end up with a NULL in there. I added a new record with just the recordID so
the rest of the columns should be NULL. I tried my original query plus the
querys suggested by Simon and they all failed to produce the desired max
value that I am looking for. I'm running php 5.2 that has sqlite 2.8.17
New Database:
recordID userID name email
1 1 John j...@somewhere.com
2 2 Mike m...@example.com
3 3 Bill b...@example.com
4 '' Steve st...@example.com
5 null null null
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> ''
SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> '' AND
userID IS NOT NULL
SELECT max(userID) AS highest_userID FROM maillist WHERE (userID <> '') AND
(userID IS NOT NULL)
SELECT max(userID) AS highest_userID FROM maillist WHERE userID NOT IN ('',
NULL)
all of the above querys return NULL with the new database, I am trying to
get it to return a 3.
Hmmmm..... any ideas what to try next? Sorry for the noob questions,
already this thread has significantly helped my understanding of sqlite and
I really apreciate the help!
----- WHY I SETUP THE DATABASE LIKE IT IS ----------------
Simon pointed out that the records should never contain NULLs. I
previously used a .CSV flat text file to store the data and am migrating
over to sqlite2. It might not be very good database design, but the reason
I do is so the users can add their data, I will check it and if it is good,
then I'll assign them a userID number. So if that field is blank or NULL,
it tells me that I have not manually validated that record yet.
Not sure if this is a good practice or not, but it has worked good with the
text files and am hoping to be able to do the same thing with sqlite.
Thanks
Shorty
> Next Question - if I use the statment:
> SELECT max(userID) AS 'highest_userID' FROM maillist WHERE userID <> ''
>
> Would that produce the same results for both NULL numeric entries and
> empty
> strings?
NULL compares smaller than any other value. So, unless all the values are
NULL, max() won't pick it.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
