Sample Header Ad - 728x90

Keep both non-overlapping intervals and overlapping ones with a larger id MySQL

0 votes
0 answers
122 views
I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique. start, end are timestamps(and end can be NULL). Say that I want to eliminate overlapping intervals but if there's any, I want to keep the ones with the highest interval_id. For the interval elimination, I am using the following criteria:
(t1.start =t2.start OR t1.end IS NULL)
I am doing a (self) CROSS JOIN and have something like this already:
SELECT t1.object_id, t1.interval_id, t1.start, t1.stop
FROM data AS t1, data AS t2
WHERE t1.object_id = t2.object_id -- we want to compare same objects
AND (t1.start =t2.start OR t1.end IS NULL)) --overlapping condition
AND t1.interval_id > t2.interval_id --keep ones with the highest id?
My main question is if this is a correct approach to keep both non-overlapping intervals and overlapping intervals with the highest interval_id or am I doing anything wrong? I wish I could fully test out myself but I currently don't have access to the db, so any input would be appreciated.
Asked by Alex.Kh (101 rep)
Nov 6, 2023, 01:09 AM