ok, just to close this. Both versions of course do work, thanks again to Michael and Igor for the help. I currently use the NOT IN variant but I think both are equivalent, maybe a speed difference but since that table is small I didn't bother to test it out.
For those non-Sql folks like me, here are the two versions: delete from THI where ID in ( select t2.ID from THI t2 where t2.UserID=THI.UserID order by t2.TimeStamp desc limit 1000000 offset 10); delete from THI where ID not in ( select t2.ID from THI t2 where t2.UserID=THI.UserID order by t2.TimeStamp desc limit 10); Best Marcus On 17.01.2011 18:38, Black, Michael (IS) wrote: > Competing solutions...gotta' love it...I prefer the postive solution as it's > a list of what WILL be deleted. Though there's some merit in "tell me what > will be left". > > delete from THI where ID in ( > select t2.ID from THI t2 where t2.UserID=UserID > order by t2.TimeStamp desc limit 1000000 offset 10); > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm > Sent: Mon 1/17/2011 11:18 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... > > > > > On 17.01.2011 17:26, Black, Michael (IS) wrote: >> I came up with the logical opposite which I think does what you want >> >> select a.id 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); >> >> You just need to set the limit value to some ridiculous number. > > ahh... nice trick!! Thank you! ;) > > Hm.. but how do I place that into a "DELETE FROM ..." statement ? > Here I can't use the alias syntax... > > > > >> >> Michael D. Black >> Senior Scientist >> Advanced Analytics Directorate >> Northrop Grumman Information Systems >> >> >> ________________________________ >> >> From: sqlite-users-boun...@sqlite.org on behalf of Marcus Grimm >> Sent: Mon 1/17/2011 10:24 AM >> To: General Discussion of SQLite Database >> Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... >> >> >> >> >> On 17.01.2011 17:14, Igor Tandetnik wrote: >>> Marcus Grimm<mgr...@medcom-online.de> wrote: >>>> I have a table to record some history data, for example >>>> items a user recently selected: >>>> >>>> CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID >>>> INTEGER, DataID INTEGER); >>>> >>>> That table needs to trace only the last 10 events, thus I would like >>>> to remove entries from all users until each user has only 10 recent entries >>>> in that table. >>>> >>>> I can delete for a specific user (42), using this: >>>> >>>> DELETE FROM THI WHERE >>>> (UserID=42) AND >>>> (ID NOT IN (SELECT ID FROM THI WHERE UserID=42 ORDER BY TimeStamp DESC >>>> LIMIT 10)); >>>> >>>> But how do I do this for all users without using a surrounding loop >>>> on application level ? >>> >>> delete from THI where ID not in ( >>> select ID from THI t2 where t2.UserID = UserId >>> order by t2.TimeStamp desc limit 10); >> >> Thanks Igor! >> but a stupid question: Doesn't this basically remove >> all entries from the table because of the "NOT IN" condition ? >> >> Marcus >> >>> >> _______________________________________________ >> 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
