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
``````

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.