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.end or t2.end IS NULL) AND (t1.end>=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.end or t2.end IS NULL) AND (t1.end>=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.
