By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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,120,'2023-06-01');
5 rows affected
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
| range_from | range_end | created_on |
|---|---|---|
| 100 | 105 | 2023-01-01 |
| 106 | 111 | 2023-06-01 |
| 112 | 120 | 2023-06-01 |
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
);
| range_from | range_end | created_on |
|---|---|---|
| 106 | 110 | 2023-01-01 |