Delete all but the newest with specific filename

I migrated a file logger to log to the database instead of to disk. This caused many duplicates in the database, whereas on disk the file would have just gotten overwritten. I want to delete all the “not newest” rows given a specific filename, what SQL would do this given the following table:

The table: log_rules

The columns: log_rules_id, file_name, file_data, create_date

I’m using SQL Server.

Pseudo SQL:

delete from log_rules where "is not neweset with file_name"

I’m trying to avoid the error where the “in clause” can’t have more than 2k entries.

Thanks!

Answer

One method uses an updatable CTE:

with todelete as (
      select lr.*,
             row_number() over (partition by file_name order by create_date desc) as seqnum
      from log_rules
     )
delete from todelete
    where seqnum > 1;