Hi,
This is something which will work in round robin fashion. I will suggest something like a trigger which will delete the older entries, when the table is updated with new data. There are some papers out for implementing rrd from sql. Google them, they should be helpful VENKAT Bug the Bugs ________________________________ From: Marcus Grimm <mgr...@medcom-online.de> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, January 17, 2011 5:47:45 PM Subject: [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
