Just curious: why cannot you use the AUTOINCREMENT option
(http://sqlite.org/autoinc.html)
Mircea
On 09/05/2012 11:38 AM, Shorty wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
