Topic: Superimposing join of records in one group

There is a big member list, each of them has 2 signs stored in fields f1 and f2
[spoiler declare @t table...]

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(9,5,888);
(10,5,999)``````

[/spoiler]
id, f1, f2
1,1,111
2,1,222
3,0,222
4,3,222
5,4,222
6,3,333
7,4,555
8,0,777
9,5,888
10,5,999
It is necessary to unite elements in one group by a following principle:
1. We search for all elements with matching value in f1 and it is united them in groups
2. Memberwise from the group which have turned out on the previous step, we search in all table for elements with the same value in f1 and we add group the found elements if at a group element in the field f1 the zero for such it is not necessary to search for search of coincidence is installed
3. If the element already is in any group for it it is not necessary to create new group i.e. if the element already got at least to one group it should not meet in other groups
The result should be such
id, f1, f2, group
1,1,111,1
2,1,222,1
3,0,222,1
4,3,222,1
5,4,222,1
6,3,333,1
7,4,555,1
8,0,777,2
9,5,888,3
10,5,999,3
Fields f1 and f2 can be eliminated from resultant sampling
The only thing that comes to mind is a repeated connection of the table with by itself and connection of results of sampling with results of sampling, turns out any game
Can eat more beautiful decision?

Recursion

Re: Superimposing join of records in one group

TJ001 wrote:

it is necessary to unite elements in one group by a following principle:
1. We search for all elements with matching value in f1 and it is united them in groups
2. Memberwise from the group which have turned out on the previous step, we search in all table for elements with the same value in f1 and we add group the found elements if at a group element in the field f1 the zero for such it is not necessary to search for search of coincidence is installed

I  did not understand one?
"All elements with matching value in f1"
"Elements with the same value in f1"
In what a difference, Charles?

Re: Superimposing join of records in one group

TJ001 wrote:

1. We search for all elements with matching value in f1 and it is united them in groups

I apologize,
Should be so:
1. We search for all elements with matching value in f2 and it is united them in groups

Re: Superimposing join of records in one group

iap wrote:

the Recursion

?
Two update consult.

Re: Superimposing join of records in one group

aleks222 wrote:

it is passed...
?
Two update consult.

1. We take all records with f1=1
Etc. while there will be nothing to add.
N. We take records with f1, not got in the set generated before.
N+1. We repeat the previous algorithm of selection from item 2.
What it, if not the recursive bypass for the amount of iterations is not known?

Re: Superimposing join of records in one group

It turned out somehow so...

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(9,5,888);
(10,5,999)
drop table if exists #tmp
select
isnull (t2.id, t1.id) id
,Dense_Rank () over (order by t1.f2) as Grp
,Count (*) over (partition by t1.f2) as GrpCnt
into #tmp
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1 &lt;&gt; 0
select distinct id
, (select top 1 Grp
from #tmp as tt
where tt.id = t.id
order by GrpCnt desc, Grp desc)
from #tmp as t
drop table if exists #tmp``````

Re: Superimposing join of records in one group

And here so it is even better

``````select id
,Grp
from (
select
id
,Grp
,GrpCnt
,row_number () over (partition by id order by GrpCnt desc, Grp desc) RN
from (
select
isnull (t2.id, t1.id) id
,Dense_Rank () over (order by t1.f2) as Grp
,Count (*) over (partition by t1.f2) as GrpCnt
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1 &lt;&gt; 0
)a) b
where RN =1``````

What tell, misters?

Re: Superimposing join of records in one group

TJ001 wrote:

and here so it is even better

``````select id
,Grp
from (
select
id
,Grp
,GrpCnt
,row_number () over (partition by id order by GrpCnt desc, Grp desc) RN
from (
select
isnull (t2.id, t1.id) id
,Dense_Rank () over (order by t1.f2) as Grp
,Count (*) over (partition by t1.f2) as GrpCnt
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1 &lt;&gt; 0
)a) b
where RN =1``````

What tell, misters?

Re: Superimposing join of records in one group

aleks222 wrote:

Good variant show?

Re: Superimposing join of records in one group

TJ001 wrote:

it is passed...
Good variant show?

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(9,5,888);
(10,5,999)
;
with t0 as (select * from @t)
, t1 as (select * from t0 where exists (select * from t0 as x where x.f2 = t0.f2 and x.id &lt;&gt; t0.id))
, t2 as (select t0. *, g = isnull (x.f2, t0.f2) from t0 outer apply (select top (1) * from t1 where f1 = t0.f1 and f1 &lt;&gt; 0 order by f2) as x)
select *, Grp = Dense_Rank () over (order by g) from t2;``````

aleks222;
And t0 what for

Re: Superimposing join of records in one group

If to use here such input data the element with id=17 falls out in the separate group which even not has been not connected with id=16, as if he on itself

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(9,5,888);
(10,5,999);
(11,6,9991);
(12,6,8881);
(13,6,99911);
(14,7,9991);
(15,7,9992);
(16,7,9993);
(17,0,9993)``````

While at all I do not know as it to win, on idea it should get to one group with 11,12,13,14,15,16

Re: Superimposing join of records in one group

aleks222;

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(11,1,777);
(9,5,888);
(10,5,999)
;``````

?

Re: Superimposing join of records in one group

TJ001;
For the data set resulted by me what should be result? Where record with id=1 should get? To 222 or 777?

Re: Superimposing join of records in one group

Pavel Vorontsov;
In an ideal all of them (: 1,2,3,4,5,6,7,11,8) should be in one group
From such dial-up on idea one group should turn out

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,2,111);
(3,2,333);
(4,4,333);
(5,4,555);
(6,0,555)``````

Re: Superimposing join of records in one group

TJ001 wrote:

Pavel Vorontsov;
In an ideal all of them (: 1,2,3,4,5,6,7,11,8) should be in one group
From such dial-up on idea one group should turn out

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,2,111);
(3,2,333);
(4,4,333);
(5,4,555);
(6,0,555)``````

"transitive communications" to consider - it is necessary  update in a cycle.
However, in your initiating description of it it is not written.

Re: Superimposing join of records in one group

aleks222;
When I opened a subject did not face yet such nuance, it was clarified later - at check of results. On my given (400 records) it met only time

Re: Superimposing join of records in one group

TJ001;

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(11,1,777);
(9,5,888);
(10,5,999)
;
with t0 (f2, f22) as (select distinct a.f2, b.f2 as f22 from @t a join @t b on a.f2 &lt;= b. F2 and a.f1 = b.f1 and a.f1! =0)
, t1 as (select a.f2, a.f22, 1 as lvl from t0 a where a.f2 &lt;&gt; a.f22
union all
select a.f2, b.f22, a.lvl+1 from t1 as a join t0 as b on b.f2 = a.f22 and a.lvl &lt;90 and b.f2 &lt;&gt; b.f22
)
, t2 as (select b.f2, b.f22, b.lvl from t1 as b - where not exists (select 1 from t1 as c where b.f2 = c.f22)
union all
select a.f2, a.f22, 0 as lvl from t0 as a where a.f22 = a.f2)
, t3 as (select min (f2) as grp, f22 from t2 group by f22)
select tt. *, t3.grp from @t tt join t3 on tt.f2 = t3.f22``````

Re: Superimposing join of records in one group

TJ001;
Corrected

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(11,1,777);
(9,5,888);
(10,5,999)
;
with t0 (f2, f22) as (select distinct a.f2, b.f2 as f22 from @t a join @t b on a.f2 &lt;= b. F2 and a.f1 = b.f1 and a.f1! =0)
, t1 as (select a.f2, a.f22, 1 as lvl from t0 a where a.f2 &lt;&gt; a.f22
union all
select a.f2, b.f22, a.lvl+1 from t1 as a join t0 as b on b.f2 = a.f22 and a.lvl &lt;90 and b.f2 &lt;&gt; b.f22
)
, t2 as (select b.f2, b.f22, b.lvl from t1 as b - where not exists (select 1 from t1 as c where b.f2 = c.f22)
union all
select distinct a.f2 as f2, a.f2 as f22, 0 as lvl from @t as a)
, t3 as (select min (f2) as grp, f22 from t2 group by f22)
select tt. *, t3.grp from @t tt join t3 on tt.f2 = t3.f22``````

Re: Superimposing join of records in one group

TJ001;
Still a variant, without cte

``````declare @t table (id int, f1 int, f2 int)
insert into @t values
(1,1,111);
(2,1,222);
(3,0,222);
(4,3,222);
(5,4,222);
(6,3,333);
(7,4,555);
(8,0,777);
(11,1,777);
(9,5,888);
(10,5,999)
;
with t0 as (select f1 from @t where f1! = 0 group by f1 having count (distinct f2)&gt; 0)
, t1 as (select tt0.f1, tt1.f1 as f2 from t0 tt0 join t0 tt1 on tt0.f1 &lt;tt1.f1
where exists (select 1 from @t t where t.f1 = tt0.f1 and exists (select 1 from @t tt where tt.f2 = t.f2 and tt.f1 = tt1.f1))
)
, t2 as (select min (tt1.f1) as grp, tt1.f2
from (select f1, f2 from t1
union all
select distinct tt2.f1, tt2.f1 as f2 from @t tt2 where f1! = 0)
tt1 group by tt1.f2)
select tt. *, isnull (isnull (t2.grp, (select min (t_in.f1) from @t t_in where t_in.f2 = tt.f2 and t_in.f1! = 0)), tt.f2) as grp
from @t tt left join t2 on tt.f1 = t2.f2``````

Re: Superimposing join of records in one group

Pavel Vorontsov;
Hurried, without cte it does not turn out. Well complete.... The same Principle, as in the first variant.

Re: Superimposing join of records in one group

Pavel Vorontsov;
Thanks! The boom to look [img=http://www.kolobok.us/smiles/standart/drinks.gif]