poOk - 2 years ago 64
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download