August 11, 2008 at 5:21 am
hi, I have this data
Descr value
----- ------
Test1 1
Test1 2
Test1 3
Test1 7
Test1 8
Test1 9
Test1 12
Test1 13
Test2 4
Test2 5
Test2 6
Test3 10
Test3 11
How can I group these values so that I can determine the ranges per description like the one below?
Descr Min Max
----- ---- -----
Test1 1 3
Test1 7 9
Test1 12 13
Test2 4 6
Test2 10 11
Thank you very much. Min() and Max() don't work...
August 11, 2008 at 5:40 am
Join the table to itself on
T1.Descr = T2.Descr AND T1.value = T2.value-1
_____________
Code for TallyGenerator
August 11, 2008 at 7:50 am
Interesting.
This is the resultset from above suggestion.
DescrValueDescrValue
Test11Test12
Test12Test13
Test13NULLNULL
Test17Test18
Test18Test19
Test19NULLNULL
Test112Test113
Test113NULLNULL
Test24Test25
Test25Test26
Test26NULLNULL
Test310Test311
Test311NULLNULLUsing this test codeDECLARE@Sample TABLE (Descr VARCHAR(20), Value INT)
INSERT@Sample
SELECT'Test1', 1 UNION ALL
SELECT'Test1', 2 UNION ALL
SELECT'Test1', 3 UNION ALL
SELECT'Test1', 7 UNION ALL
SELECT'Test1', 8 UNION ALL
SELECT'Test1', 9 UNION ALL
SELECT'Test1', 12 UNION ALL
SELECT'Test1', 13 UNION ALL
SELECT'Test2', 4 UNION ALL
SELECT'Test2', 5 UNION ALL
SELECT'Test2', 6 UNION ALL
SELECT'Test3', 10 UNION ALL
SELECT'Test3', 11
SELECT*
FROM@Sample AS s1
LEFT JOIN@Sample AS s2 ON s2.Descr = s1.Descr
AND s2.Value = s1.Value + 1
How will you easily group the requested sequencies, Sergiy?
N 56°04'39.16"
E 12°55'05.25"
August 11, 2008 at 7:54 am
For alternative solutions, see
-- Prepare sample data
DECLARE@Sample TABLE
(
Descr VARCHAR(20),
Value INT,
PRIMARY KEY CLUSTERED
(
Descr,
Value
),
Seq INT
)
INSERT@Sample
(
Descr,
Value
)
SELECT'Test1', 1 UNION ALL
SELECT'Test1', 2 UNION ALL
SELECT'Test1', 3 UNION ALL
SELECT'Test1', 7 UNION ALL
SELECT'Test1', 8 UNION ALL
SELECT'Test1', 9 UNION ALL
SELECT'Test1', 12 UNION ALL
SELECT'Test1', 13 UNION ALL
SELECT'Test2', 4 UNION ALL
SELECT'Test2', 5 UNION ALL
SELECT'Test2', 6 UNION ALL
SELECT'Test3', 10 UNION ALL
SELECT'Test3', 11
-- Initialize staging
DECLARE@Seq INT,
@Descr VARCHAR(20),
@Value INT
UPDATE@Sample
SET@Seq = Seq =CASE
WHEN @Seq IS NULL THEN 0
WHEN Descr = @Descr AND Value = @Value + 1 THEN @Seq
ELSE @Seq + 1
END,
@Descr = Descr,
@Value = Value
-- Show the expected output
SELECTDescr,
MIN(Value) AS Min,
MAX(Value) AS Max
FROM@Sample
GROUP BYDescr,
Seq
ORDER BYDescr,
Seq
N 56°04'39.16"
E 12°55'05.25"
August 11, 2008 at 2:26 pm
Hi Peso, thank you very much for this, it saved me another wasted day trying to find ways to make this work. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply