Thank you very much Michael and Igor for taking your time! I'm still not sure if Igor's last version will work but I'll not start to argue with Igor about Sql! :-)
Nor do I know how to put Michaels solution in a DELETE statement. I have to leave for today, but I'll come back to this after I tried the two solutions and report back. Thanks again for a enlightening afternoon. Marcus On 17.01.2011 18:04, Black, Michael (IS) wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
