SELECT rows where the numeric range specified by two fields (start-end) does not overlap with the range specified in other rows
I have the following table
id score start_time end_time
1 60 25 30
2 85 5 10
3 90 10 15
4 100 0 20
I would like to fulfill a request
SELECT * FROM table
WHERE the range between start_time and end_time doesn't overlap with anything in the result set ordered by score DESC
So, in this case, the result set would be:
id score start_time end_time
4 100 0 20
1 60 25 30
since the range between start_time
and end_time
for table.id =2
and table.id =3
overlaps with the range between start_time
and end_time
for table.id =4
, and score
of is table.id =4
greater than the estimate table.id =2
andtable.id =3
Can this be done strictly through mysql?
source to share
EDIT: Sorry, I had a small error. Now it really works.
Test data setup:
create table test(
id int,
score int,
start_time int,
end_time int
);
insert into test values
(5, 95, 0, 15), /*extra test case from me*/
(1, 60, 25, 30),
(2, 85, 5, 10),
(3, 90, 10, 15),
(4, 100, 0, 20)
;
Required function:
DELIMITER $$
DROP FUNCTION IF EXISTS checkOverlap$$
CREATE FUNCTION checkOverlap(p_id INT, p_score INT, p_stime INT, p_etime INT)
RETURNS BOOL
READS SQL DATA
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE result INT DEFAULT TRUE;
DECLARE stime, etime INT;
DECLARE cur1 CURSOR FOR SELECT start_time, end_time FROM test WHERE id != p_id AND score > p_score;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO stime, etime;
IF done THEN
LEAVE read_loop;
END IF;
IF ((p_stime >= stime AND p_stime <= etime) OR (p_etime >= stime AND p_etime <= etime)) THEN
SET result = FALSE;
END IF;
END LOOP;
CLOSE cur1;
RETURN result;
END$$
DELIMITER ;
How to use the function:
select *
from
test
where
checkOverlap(id, score, start_time, end_time) = TRUE
order by score desc
PS: Really good question. It was fun to decide
source to share