Thanks Venkat and Michael, actually I'm doing this in fact only once per day during administration hours (i.e. in the night). Thanks for the trigger advice, I didn't yet consider a trigger for that purpose to keep the schema simple and reduce the (little) overhead involved when inserts are made into the DB. But I might change that. However, from the replies I conclude that it is not so easy doing this in pure sql; I wasn't sure about that as I'm not really good in sql. ;)
Marcus On 17.01.2011 15:24, Black, Michael (IS) wrote: > Seem to me that putting it inside a trigger would be the best choice...then > you don't have to worry about it unless you have speed concerns and only want > to do this once a day or such. > > CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER, > DataID INTEGER); > create trigger t1 after insert on thi > begin > delete from thi WHERE (UserID=new.UserID) AND (ID NOT IN (SELECT ID FROM THI > WHERE UserID=new.UserID ORDER BY TimeStamp DESC LIMIT 10)); > end; > > As long as you have an index on UserID this should be pretty fast. > > 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 6:17 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:[sqlite] Help on DELETE FROM... > > > > 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 > > > > > > _______________________________________________ > 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
