Hmmm...works for me....are you SURE you're using the correct database after you made the changes?
I used the 2.8.17 shell that I got from http://www.gamefront.com/files/service/thankyou?id=4833830 and it works just fine. 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); This is the query that you want...you should be explicit to be sure...though it seems the NOT NULL isn't necessary. sqlite> SELECT max(userID) AS highest_userID FROM maillist WHERE userID <> '' and userID IS NOT NULL; 3 sqlite> SELECT recordid,userID AS highest_userID FROM maillist WHERE userID is not null; 1|1 2|2 3|3 4| sqlite> SELECT recordid,userID AS highest_userID FROM maillist WHERE userID <> ''; 1|1 2|2 3|3 sqlite> SELECT recordid,userID AS highest_userID FROM maillist; 1|1 2|2 3|3 4| 5| Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Shorty [sho...@shortypen.com] Sent: Wednesday, May 09, 2012 10:38 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] max(userID) .. WHERE userID IS NOT NULL returns NULL But... WHERE <> '' returns value 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
