CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

Get same range value order by column

I have a table

ID Value
1  2
2  3
3  3
4  3
5  2
6  2
7  1

I want to group value but still order by ID, like this

ID   Value
1    2
2-4  3
5-6  2
7    1

How can I do this?

Upvotes: 2

Views: 39

Answers (1)

Devart
Devart

Reputation: 122002

DECLARE @t TABLE (
    ID INT PRIMARY KEY,
    Value INT
)

INSERT INTO @t (ID, Value)
VALUES (1,2),(2,3),(3,3),(4,3),(5,2),(6,2),(7,1)

SELECT 
    ID =
        CASE WHEN mx = mn
            THEN CAST(mx AS VARCHAR(10))
            ELSE CAST(mn AS VARCHAR(10)) + '-' + CAST(mx AS VARCHAR(10))
        END,
    Value
FROM (
    SELECT group_id, mx = MAX(ID), mn = MIN(ID), Value = MAX(Value)
    FROM (
        SELECT
              *
            , group_id =
                ROW_NUMBER() OVER (ORDER BY ID) -
                ROW_NUMBER() OVER (PARTITION BY Value ORDER BY ID)
        FROM @t
    ) t
    GROUP BY group_id
) t

output - 

ID    Value
----- -----------
1     2
2-4   3
5-6   2
7     1

Upvotes: 5

Related Questions