The "NOT IN" approach doesn't work..here's sample data using select rather than delete to show the result. sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, DataID INTEGER); INSERT INTO "THI" VALUES(0,10,10,0); INSERT INTO "THI" VALUES(1,11,10,0); INSERT INTO "THI" VALUES(2,12,10,0); INSERT INTO "THI" VALUES(3,13,10,0); INSERT INTO "THI" VALUES(4,14,10,0); INSERT INTO "THI" VALUES(5,15,10,0); INSERT INTO "THI" VALUES(6,16,10,0); INSERT INTO "THI" VALUES(7,17,10,0); INSERT INTO "THI" VALUES(8,18,10,0); INSERT INTO "THI" VALUES(9,19,10,0); INSERT INTO "THI" VALUES(10,20,10,0); INSERT INTO "THI" VALUES(11,21,10,0); INSERT INTO "THI" VALUES(12,22,10,0); INSERT INTO "THI" VALUES(13,21,20,0); INSERT INTO "THI" VALUES(14,22,20,0); INSERT INTO "THI" VALUES(15,23,20,0); INSERT INTO "THI" VALUES(16,24,20,0); INSERT INTO "THI" VALUES(17,25,20,0); INSERT INTO "THI" VALUES(18,26,20,0); INSERT INTO "THI" VALUES(19,27,20,0); INSERT INTO "THI" VALUES(20,28,20,0); INSERT INTO "THI" VALUES(21,29,20,0); INSERT INTO "THI" VALUES(22,30,20,0); INSERT INTO "THI" VALUES(23,31,20,0); INSERT INTO "THI" VALUES(24,32,20,0); INSERT INTO "THI" VALUES(25,33,20,0); COMMIT; sqlite> select * from THI where ID not in ( ...> select ID from THI t2 where t2.UserID = UserId ...> order by t2.TimeStamp desc limit 10); 0|10|10|0 1|11|10|0 2|12|10|0 3|13|10|0 4|14|10|0 5|15|10|0 6|16|10|0 7|17|10|0 8|18|10|0 9|19|10|0 10|20|10|0 11|21|10|0 12|22|10|0 13|21|20|0 14|22|20|0 15|23|20|0 Whereas the inclusion approach does work sqlite> select * from thi as a where a.id in ...> (select thi.id from thi where a.userid=thi.userid ...> order by timestamp limit 1000000 offset 10); 10|20|10|0 11|21|10|0 12|22|10|0 23|31|20|0 24|32|20|0 25|33|20|0 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik Sent: Mon 1/17/2011 10:57 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... Marcus Grimm <mgr...@medcom-online.de> wrote: >> delete from THI where ID not in ( >> select ID from THI t2 where t2.UserID = UserId >> order by t2.TimeStamp desc limit 10); A correction: I think the subselect should say "select t2.ID from ..." > Thanks Igor! > but a stupid question: Doesn't this basically remove > all entries from the table because of the "NOT IN" condition ? It removes all records from the table that meet the condition in the WHERE clause - in other words, those whose IDs don't appear in the subselect. Have you tested this statement and found that it didn't satisfy your requirements? -- Igor Tandetnik _______________________________________________ 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
