Search code examples
sqlsql-deleterow-number

SQL Remove a row of data based on 2 date fields in previous row

I've searched through stack overflow but I can't find exactly what I'm trying to accomplish. I have some aggregated data that looks like the below (rn is a field populated using Row Number):

enter image description here

What I would like to do is write a query that would delete the 2nd row of data since the StartDate is greater than the previous rows startdate AND the EndDate is less than the previous rows EndDate. So I would be left with 2 records for this dataset, the 1st and 3rd row as they're startdate and enddate are not within the same timeframe as the previous row. Also this should be a dynamic delete and not something such as "DELETE * from table where rn = 2".

Any help is greatly appreciated and please let me know if more information is required. Thanks guys!

Solution 
  • delete from T
where exists (
    select 1 from T t2
    where   t2.org_id = T.org_id and t2.user_id = T.user_id
        and t2.startdate < T.startdate and t2.enddate > T.enddate)
);