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
