Hi List, sorry for not being very sqlite specific here but I would like to have an advice on a delete operation for which I can't find the right sql command. Currently I do it on C programming level using a loop but I think there must be a better sql way.
Anyway, here is the story: 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 ? Thank you Marcus _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
