DELETE FROM history WHERE 2 < (SELECT count() FROM history n WHERE n.id=history.id and n.lastactivity > history.lastactivity);
-----Ursprüngliche Nachricht----- Von: Patrik Nilsson [mailto:nipatriknils...@gmail.com] Gesendet: Mittwoch, 22. Mai 2013 10:48 An: General Discussion of SQLite Database Betreff: [sqlite] Deleting with offset when grouping Hi All, I can't figure out how to delete with offset using groups. Imagine I have a table create table history(id integer, lastactivity datetime default (datetime('now'))) and I insert values as insert into history (id) values (1) A resulting table might look 1|2013-05-22 07:50:05 1|2013-05-22 07:50:08 1|2013-05-22 07:50:10 1|2013-05-22 07:50:12 2|2013-05-22 07:50:16 1|2013-05-22 07:50:18 2|2013-05-22 07:50:20 2|2013-05-22 07:50:21 2|2013-05-22 07:50:22 1|2013-05-22 07:50:24 3|2013-05-22 07:50:34 1|2013-05-22 07:50:36 3|2013-05-22 07:50:37 3|2013-05-22 07:50:39 1|2013-05-22 07:50:41 6|2013-05-22 07:58:24 Now I would like to delete the oldest rows if a group of id has more than three, but I would like to keep the youngest ones in each id group. I can delete with offset for the overall table. Is this possible for a group? The lastactivity column is unique. Cheers, Patrik -- ASCII ribbon campaign ( ) against HTML e-mail X www.asciiribbon.org / \ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
