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);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
