Delete registers with rownumber greater than specified for each group got from sql

I have a table of people with their commments on a blog . I need to leave the last 10 comments for each person in the table and delete the older ones. Lets say the columns are:

• personId
• commentId
• dateFromComment

I know how to do it with several queries but not with just one query(any subqueries allowed) and for anyDatabase

With:

select personId from PeopleComments 
group by personId
having count(*) >10 

I would get the people ids who have more than 10 comments but I dont know how to get the comments Ids from there and delete them

Thanks!

Answer

In my other answer the DBMS must find and count rows for every row in the table. This can be slow. It would be better to find all rows we want to keep once and then delete the others. Hence this additional answer.

The following works for Oracle as of version 12c:

delete from peoplecomments
where rowid not in
(
  select rowid
  from peoplecomments
  order by row_number() over (partition by personid order by datefromcomment desc)
  fetch first 10 rows with ties
);

Apart from ROWID this is standard SQL.

In other DBMS that support window functions and FETCH WITH TIES:

• If your table has a single-column primary key, you’d replace ROWID with it.
• If your table has a composite primary key, you’d use where (col1, col2) not in (select col1, col2 ...) provided your DBMS supports this syntax.