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. 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
