poOk poOk - 7 months ago 9
SQL Question

SQL: How do dynamically parse ranges from min to next min?

I am trying to figure out how (or see if possible) I can grab the minimum value of 'TEST' and get just the next minimum value of 'TEST', then join again and go after the 2nd minimum value, etc, etc.

Table1

ID TEST
A12 1
A12 2
A12 3
A12 5
A12 8
B35 1
B35 3


Results I'm after:

ID RANGE1 RANGE2
A12 1 2
A12 2 3
A12 3 5
A12 5 8
B35 1 3


table code I'm using:

WITH FRED AS
(
SELECT 'A12' AS ID
, 1 AS TEST
UNION
SELECT 'A12' AS ID
, 2 AS TEST
UNION
SELECT 'A12' AS ID
, 3 AS TEST
UNION
SELECT 'B35' AS ID
, 1 AS TEST
UNION
SELECT 'B35' AS ID
, 2 AS TEST
)
SELECT *
FROM FRED F

Answer

This works for me:

--Construct sample data.
WITH FRED AS
(
SELECT  'A12' AS ID
    , 1 AS TEST
UNION
SELECT  'A12' AS ID
    , 2 AS TEST
UNION   
SELECT  'A12' AS ID
    , 3 AS TEST
UNION
SELECT  'A12' AS ID
    , 5 AS TEST
UNION
SELECT  'A12' AS ID
    , 8 AS TEST
UNION   
SELECT  'B35' AS ID
    , 1 AS TEST
UNION   
SELECT  'B35' AS ID
    , 3 AS TEST
),

--This is the sample data
test_data AS
(
SELECT  *
FROM    FRED F
ORDER BY ID, TEST
)

SELECT  td.ID, td.TEST as RANGE1, MIN(td2.TEST) as RANGE2

FROM    test_data td
    cross join test_data td2

WHERE   td.ID = td2.ID
    AND
    td.TEST < td2.TEST

GROUP BY    td.ID, td.TEST

ORDER BY td.ID, td.TEST

test_data gives the sample you provided. I can try to handle repeated value cases once you let us all know how you want those to be handled. The current code above doesn't do that.

Comments