Re: SQL competition: the shortest interval coalesce/pack query

LAG and LEAD are specific to oracle, but this will run on DB2 just as well

CREATE TABLE Intervals
(

x INT NOT NULL,
y INT NOT NULL,
CHECK (y > x),
PRIMARY KEY (x,y)
);

assuming that the intervals don't overlap,

SELECT
left_end.x left_end,
right_end.x right_end
FROM
(SELECT x,

ROW_COUNT() OVER(ORDER BY x)end_number
FROM Intervals WHERE x NOT IN(SELECT y FROM Intervals)) left_end JOIN
(SELECT y,

ROW_COUNT() OVER(ORDER BY y)end_number
FROM Intervals WHERE y NOT IN(SELECT x FROM Intervals)) right_end ON left_end.end_number = right_end.end_number

performs very well on both DB2 and Oracle, provided there are unique indexes on x and y

if the intervals can overlap, the NOT IN condition can be replaced with slightly more complicated one Received on Sun Dec 19 2004 - 21:19:08 CST