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 itself is unique.
end are timestamps(and
end can be
Say that I want to eliminate overlapping intervals but if there's any, I want to keep the ones with the highest
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.