id --- from --- to
6 5 31
6 31 30
6 30 29
6 29 28
7 56 59
7 59 60
etc...

id --- from --- to
6 5 28
7 56 60
etc...

quote:

---

Originally posted by madhivanan


select id,min([from]) as [from], min([to]) as [to] from your_table
group by id

Madhivanan

Failing to plan is Planning to fail

---

declare @t table (id int, vfrom int, vto int)
insert @t
          select 6, 5, 31
union all select 6, 31, 30
union all select 6, 30, 29
union all select 6, 29, 28
union all select 7, 56, 59
union all select 7, 59, 60

; with
    a as (select id, vfrom from @t a where not exists (select * from @t where id = a.id and vto = a.vfrom)),
    b as (select id, vto from @t a where not exists (select * from @t where id = a.id and vfrom = a.vto)),
    c as (select id, vfrom, null as vto from a union all select id, null, vto from b)
select id, max(vfrom) as vfrom, max(vto) as vto from c group by id

/* Results
id          vfrom       vto
----------- ----------- -----------
6           5           28
7           56          60
*/

declare @t table (id int, vfrom int, vto int)
insert @t
          select 2, 10, 11
union all select 2, 16, 19
union all select 2, 19, 18
union all select 2, 18, 20
union all select 2, 20, 8
union all select 5, 10, 11
union all select 28, 12, 11
union all select 28, 16, 12
union all select 29, 12, 11
union all select 46, 12, 11
union all select 46, 22, 16
union all select 46, 21, 22
union all select 47, 12, 11
union all select 48, 12, 11
union all select 58, 28, 27
union all select 58, 29, 28
union all select 58, 30, 29
union all select 58, 31, 30
union all select 58, 5, 31
union all select 61, 26, 21
union all select 61, 27, 26
union all select 65, 31, 34

; with
    t1 (id, vInitial, vfrom, vto, Level) as (
        select id, vfrom, vfrom, vto, 1 from @t a where not exists (select * from @t where id = a.id and vto = a.vfrom)
        union all
        select a.id, vInitial, b.vfrom, b.vto, Level + 1 from t1 a inner join @t b on a.id = b.id and a.vto = b.vfrom
    ),
    t2 as (select *, row_number() over (partition by id, vInitial order by Level desc) as RowNumber from t1)
select id, vInitial as vfrom, vto from t2 where RowNumber = 1

/* Results
id          vfrom       vto
----------- ----------- -----------
2           10          11
2           16          8
5           10          11
28          16          11
29          12          11
46          12          11
46          21          16
47          12          11
48          12          11
58          5           27
61          27          21
65          31          34
*/