Write a SQL Query to find start and end values of a continuous sequence of an Element

 

Expected Output:

 

CREATE TABLE ELEMENT(

ELEMENT VARCHAR2(10),

START_SEQ NUMBER,

END_SEQ NUMBER

);INSERT INTO ELEMENT VALUES('A','1','2');

INSERT INTO ELEMENT VALUES('A','2','3');

INSERT INTO ELEMENT VALUES('A','4','5');

INSERT INTO ELEMENT VALUES('A','5','6');

INSERT INTO ELEMENT VALUES('A','6','7');

INSERT INTO ELEMENT VALUES('B','8','9');

INSERT INTO ELEMENT VALUES('B','9','10');

INSERT INTO ELEMENT VALUES('C','11','12');

commit;

 

WITH TEMP AS(

SELECT

   ELEMENT, START_SEQ, END_SEQ,

   ROW_NUMBER() OVER(PARTITION BY ELEMENT ORDER BY START_SEQ,END_SEQ) AS ELEMENT_SEQ

FROM ELEMENT

)

,TEMP2 AS(

SELECT

   ELEMENT, START_SEQ, END_SEQ,

   (START_SEQ - ELEMENT_SEQ) AS START_DIFF

FROM TEMP

)

SELECT

   ELEMENT,

   MIN(START_SEQ) AS MIN_SEQ,

   MAX(END_SEQ) AS MAX_SEQ

FROM TEMP2

GROUP BY ELEMENT,START_DIFF

ORDER BY ELEMENT;