Find older records in a group in a SQL query

Question

I'm trying to find older records within a group of a SQL table, so that I can delete them. But I'm having trouble even selecting them.

Table looks like this:

What I want to do is keep only the top 3 newest changes on each issue, and I want to delete the older records.

What is the SQL required to find the ones not in the top 3 most recent for each issue? Thanks!

Answer 1

What is the SQL required to find the ones not in the top 3 most recent for each issue?

If you are running MySQL 8.0, use row_number():

select *
from (
    select 
        t.*, 
        row_number() over(partition by issue_id order by change_date desc) rn
    from mytable t
) t
where rn > 3

In ealier versions, you can emulate this with a correlated subquery:

select *
from mytable t
where (
    select count(*) 
    from mytable t1 
    where t1.issue_id = t.issue_id and t1.change_date > t.change_date
) >= 3