Thanks, nice and simple. I had come up with something that works as well, but probably more complex than needed. This is with my actual data:
delete from sqlite_tablePa where rowid not in (select rowid from sqlite_tablePa where (select count(*) from sqlite_tablePa as s where s.patient_id = sqlite_tablePa.patient_id and s.rowid < sqlite_tablePa.rowid) < 3) I think I will stick with your solution as it is simpler and most likely faster as well. RBS -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: 03 July 2008 22:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] delete to leave x rows per group RB Smissaert <[EMAIL PROTECTED]> wrote: > Can this be done in SQLite SQL? > > ID Value > --------------- > 1 A > 1 B > 1 C > 1 D > 1 E > 2 A > 2 B > 2 C > 2 D > 2 E > 2 F > > Delete rows to leave x rows per ID, say 3 rows, so we get: delete from tableName where rowid not in ( select rowid from tableName t2 where t2.ID = tableName.ID order by Value desc limit 3 ); Igor Tandetnik _______________________________________________ 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
