SQL recursive cte - sort overlapping range

Question

I have a table like below

range_from range_end created_on
100 105 2023-01-01
106 110 2023-01-01
106 111 2023-06-01
120 130 2023-01-01
112 130 2023-06-01

I need to clean it to get a most updated without overlapping table.

The result should look like this

range_from range_end created_on
100 105 2023-01-01
106 111 2023-06-01
112 130 2023-06-01

The problem I have is I don't know how to let the loop keep running after 'where' or how to delete a row under select statement. My code is:

with recursive bin_range as (
        select bin_from, bin_end, created_on,
                       row_number() over(order by bin_from) rownum,
                from raw_BIN
                qualify rownum =1
  
         union all

        select v.bin_from, v.bin_end, v.created_on, 
               v.rownum
                from bin_range B
                join ( select bin_from, bin_end, created_on,
                        row_number() over(order by bin_from) rownum,
                         from raw_BIN
                )V on B.rownum +1 = V.rownum
        where v.bin_from < b.bin_end
)
select * from bin_range;

Thank you so much!!!

Can the earlier range be larger than the latter? How would this look if your first record was 99 106 2022-12-31. Would it just be completely discarded, or would you have a single record from 99 to 99 for the dangling bit? This is very similar to this question stackoverflow.com/questions/77525077/… . That Simeon Pilgrim gave a very nice answer for, circumventing the recursive cte and my boneheaded date explosion and restitch. — Error_2646
– Error_2646, Commented Nov 29, 2023 at 22:29

Error_2646 · Accepted Answer · 2023-11-29 23:09:08Z

0

This is how I'm interpreting what you are looking for. Of course this assumes your sample represents all the complexity you need to handle.

Since you didn't tag a DBMS, this is in SQL Server. But this should run fine on just about anything.

Note that will discard rows that overlap at all with a record with a later create time.

https://dbfiddle.uk/cYrHc9Gw

create table raw_BIN  
  ( range_from integer,
    range_end integer,
    created_on date);

insert into raw_BIN  values (100,105,'2023-01-01');
insert into raw_BIN  values (106,110,'2023-01-01');
insert into raw_BIN  values (106,111,'2023-06-01');
insert into raw_BIN  values (120,130,'2023-01-01');
insert into raw_BIN  values (112,130,'2023-06-01');

SELECT
  *
FROM
  raw_BIN t1
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      raw_BIN t2
    WHERE
      t2.created_on > t1.created_on
      AND t1.range_end >= t2.range_from
      AND t1.range_from <= t2.range_end
  )
order
   by range_from,
      range_end

edited Nov 29, 2023 at 23:09

answered Nov 29, 2023 at 22:50

Error_2646

3,8991 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yotheguitou Over a year ago

Last one is 112-130

Error_2646 Over a year ago

@yotheguitou Ah, good catch. Fixed that in the fiddle but not here. Thanks.

Error_2646 Over a year ago

@HHan Glad to hear it! Please accept the answer if it works for you so others know they can move on.

yotheguitou · Accepted Answer · 2023-11-29 22:24:43Z

0

You don't need recursive cte :

SELECT *
FROM raw_BIN t1
WHERE EXISTS (
    SELECT *
    FROM raw_BIN t2
    WHERE (t1.range_from <> t2.range_from OR t1.range_end <> t2.range_end)
    AND t1.range_from BETWEEN t2.range_from AND t2.range_end
    AND t1.range_end BETWEEN t2.range_from AND t2.range_end
);

answered Nov 29, 2023 at 22:24

yotheguitou

5293 silver badges8 bronze badges

4 Comments

Error_2646 Over a year ago

Does this work? If we just want to discard any preceeding overlapping interval I was thinking it'd look like this: SELECT * FROM raw_BIN MINUS SELECT * FROM raw_BIN t1 WHERE EXISTS ( SELECT * FROM raw_BIN t2 WHERE t2.created_on > t1.created_on AND t1.range_end >= t2.range_from AND t1.range_from <= t2.range_end );

Error_2646 Over a year ago

Or NOT EXISTS instead of MINUS :).

yotheguitou Over a year ago

@Error_2646 It works to show overlap. So it need to be transform to DELETE to clean table.

Error_2646 Over a year ago

Ah, I see where you are going. Was figuring it was dangerous to assume one of the intervals is going to be totally enveloped by the other and not just disjoint overlapping. Like if we had (120,131,'2023-01-01') instead of (120,130,'2023-01-01'). But yeah, OPs sample data doesn't have that scenario.

Collectives™ on Stack Overflow

SQL recursive cte - sort overlapping range

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related