Thank you very much. It works. Vielen Dank, Patrik
On 05/22/2013 12:22 PM, Hick Gunter wrote: > > 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 > -- 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
