Write a SQL Query to find Min and Max values of continuous sequence in a group of elements

 

 Expected Output:

 

 

CREATE TABLE ELEMENTS(

   ELEMENT VARCHAR2(10),

   SEQUENCE NUMBER

);

 

INSERT INTO ELEMENTS VALUES('A','1');

INSERT INTO ELEMENTS VALUES('A','2');

INSERT INTO ELEMENTS VALUES('A','3');

INSERT INTO ELEMENTS VALUES('A','5');

INSERT INTO ELEMENTS VALUES('A','6');

INSERT INTO ELEMENTS VALUES('A','8');

INSERT INTO ELEMENTS VALUES('A','9');

INSERT INTO ELEMENTS VALUES('B','11');

INSERT INTO ELEMENTS VALUES('C','13');

INSERT INTO ELEMENTS VALUES('C','14');

INSERT INTO ELEMENTS VALUES('C','15');

COMMIT;

 

SELECT

   ELEMENT,

   SEQUENCE,

   ROW_NUMBER() OVER(PARTITION BY ELEMENT ORDER BY SEQUENCE) AS ELEMENT_SEQ

FROM ELEMENTS;

 

WITH TEMP AS(

SELECT

   ELEMENT,

   SEQUENCE,

   ROW_NUMBER() OVER(PARTITION BY ELEMENT ORDER BY SEQUENCE) AS ELEMENT_SEQ

FROM ELEMENTS

)

SELECT

   ELEMENT,

   SEQUENCE,

   ELEMENT_SEQ,

   (SEQUENCE - ELEMENT_SEQ) AS DIFF

FROM TEMP;

 

WITH TEMP AS(

SELECT

   ELEMENT,

   SEQUENCE,

   ROW_NUMBER() OVER(PARTITION BY ELEMENT ORDER BY SEQUENCE) AS ELEMENT_SEQ

FROM ELEMENTS

)

,TEMP2 AS(

SELECT

   ELEMENT,

   SEQUENCE,

   ELEMENT_SEQ,

   (SEQUENCE - ELEMENT_SEQ) AS DIFF

FROM TEMP

)

SELECT

   ELEMENT,

   MIN(SEQUENCE) AS MIN_SEQ,

   MAX(SEQUENCE) AS MAX_SEQ

FROM TEMP2

GROUP BY ELEMENT,DIFF;