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
