Competing solutions...gotta' love it...I prefer the postive solution as it's a list of what WILL be deleted. Though there's some merit in "tell me what will be left". delete from THI where ID in ( select t2.ID from THI t2 where t2.UserID=UserID order by t2.TimeStamp desc limit 1000000 offset 10); 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 11:18 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... On 17.01.2011 17:26, Black, Michael (IS) wrote: > I came up with the logical opposite which I think does what you want > > select a.id from thi as a where a.id in (select thi.id from thi where > a.userid=thi.userid order by timestamp limit 1000000 offset 10); > > You just need to set the limit value to some ridiculous number. ahh... nice trick!! Thank you! ;) Hm.. but how do I place that into a "DELETE FROM ..." statement ? Here I can't use the alias syntax... > > 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 10:24 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM... > > > > > 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 > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Marcus Grimm MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt Tel: +49(0)6151-95147-10 Fax: +49(0)6151-95147-20 web: www.medcom-online.de -------------------------------------------------- MedCom slogans of the month: "Vacation ? -- Every day at MedCom is a paid vacation!" "Friday I have monday in my mind." "MedCom -- Every week a vacation, every day an event, every hour a cliffhanger, every minute a climax." "Damned, it's weekend again!" _______________________________________________ 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
