KESAVAN PURUSOTHAMAN KESAVAN PURUSOTHAMAN - 2 months ago 9
MySQL Question

How to get Start and End IDs of Continues data in Value column?

Hi I have following data in the table as below:

Source Table

I am looking for below output,

Output

I am using SQL Server 2008 R2.
I just tried with self joining the table with ID1 = ID2+1 and Values not equal conditions.

Select T1.ID as ID1 ,T1.Value V1 ,T2.ID as ID2, T2.Value as V2 From [Test_Seq] T1
INNER Join [Test_Seq] T2
on T2.ID = T1.ID+1
Where T1.Value <>T2.Value


its giving me below output,

Result Set

I hope I will get better answer here with clear explanation on this kind of scenario.

Thanks

Answer

You can try using this query

;with cte as (
select *, row_number() over(partition by value order by id) as rn1,  row_number() over(order by id) rn from #startend
) , cte2 as (
select min(id) startid, max(id) as endid, value, rn-rn1 as r from cte
group by rn-rn1, value
) select startid, endid, value from cte2
order by startid
Comments