1.
WITH TBL
AS
(
SELECT 'A' AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 'B' AS A, 3 AS B, 4 AS C FROM DUAL
UNION ALL
SELECT 'A' AS A, 5 AS B, 6 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 7 AS B, 8 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 8 AS B, 9 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 9 AS B, 10 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 10 AS B, 11 AS C FROM DUAL
UNION ALL
SELECT 'B' AS A, 11 AS B, 12 AS C FROM DUAL
UNION ALL
SELECT 'B' AS A, 12 AS B, 13 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 13 AS B, 14 AS C FROM DUAL
UNION ALL
SELECT 'C' AS A, 14 AS B, 15 AS C FROM DUAL
)
SELECT A
, MIN(B) AS B
, MAX(C) AS C
FROM (SELECT A
, B
, C
, SUM(DECODE(A, A_LAG, 0, 1)) OVER (ORDER BY RNUM) AS TYPE
FROM (SELECT A
, B
, C
, LAG(A, 1, 'A') OVER (ORDER BY ROWNUM) AS A_LAG
, ROWNUM AS RNUM
FROM TBL))
GROUP BY A, TYPE
ORDER BY B;
A B C
- ---------- ----------
A 1 2
B 3 4
A 5 6
C 7 8
C 8 9
C 9 10
C 10 11
B 11 12
B 12 13
C 13 14
C 14 15
A B C A_LAG RNUM
- ------- ------- ------- -------
A 1 2 A 1
B 3 4 A 2
A 5 6 B 3
C 7 8 A 4
C 8 9 C 5
C 9 10 C 6
C 10 11 C 7
B 11 12 C 8
B 12 13 B 9
C 13 14 B 10
C 14 15 C 11
A B C TYPE
- ------- ------- ------
A 1 2 0
B 3 4 1
A 5 6 2
C 7 8 3
C 8 9 3
C 9 10 3
C 10 11 3
B 11 12 4
B 12 13 4
C 13 14 5
C 14 15 5
A B C
- ---------- ----------
A 1 2
B 3 4
A 5 6
C 7 11
B 11 13
C 13 15
2.
WITH TBL AS
( SELECT 100 AS sval, 110 AS eval FROM DUAL UNION ALL
SELECT 120 AS sval, 150 AS eval FROM DUAL UNION ALL
SELECT 135 AS sval, 140 AS eval FROM DUAL UNION ALL
SELECT 150 AS sval, 160 AS eval FROM DUAL UNION ALL
SELECT 170 AS sval, 180 AS eval FROM DUAL
)
SELECT MIN(SVAL) AS SVAL
, MAX(EVAL) AS EVAL
FROM ( SELECT SVAL
, EVAL
, SUM(DECODE(SIGN(PVAL - SVAL), -1, 1, 0)) OVER (ORDER BY NO) AS TYPE
FROM ( SELECT SVAL
, EVAL
, LAG(EVAL, 1, EVAL) OVER (ORDER BY EVAL) AS PVAL
, ROW_NUMBER() OVER (ORDER BY EVAL) AS NO
FROM TBL))
GROUP BY TYPE
ORDER BY SVAL;
SVAL EVAL
---------- ----------
100 110
120 150
135 140
150 160
170 180
SVAL EVAL PVAL NO
------- ------- ------- ------
100 110 110 1
135 140 110 2
120 150 140 3
150 160 150 4
170 180 160 5
SVAL EVAL TYPE
------- ------- -------
100 110 0
135 140 1
120 150 1
150 160 1
170 180 2
SVAL EVAL
---------- ----------
100 110
120 160
170 180