July 8, 2011 at 4:12 am
I have a table data as:
Rec1 Rec2
----- -----
1 2
2 3
4 3
5 6
I want to group all the relating records with the minimum value of the related records
i.e.
1 relates to 2 thus minimum is 1
2 relates to 3 BUT 2 also matches 1 so minimum becomes 1
4 relates to 3 BUT 3 relates to 2 and 2 relates to 1 so minimum becomes 1
5 relates to 6 so minimum becomes 5
I am expecting the result to be:
Rec1 Rec2 Grouping
----- ----- -------
1 2 1
2 3 1
4 3 1
5 6 5
Can anyone help me with a T-SQL statement which can achieve this result?
July 9, 2011 at 1:47 am
Finally figured this out using recursive CTE
July 9, 2011 at 4:14 am
Full Solution is:
CREATE TABLE dbo.fuzzymatches
(
Record1 nvarchar(50),
Record2 nvarchar(50)
)
GO
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('1', '2')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('2', '3')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('4', '3')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('5', '6')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('6', '7')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('8', '7')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('8', '9')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('9', '10')
INSERT INTO dbo.fuzzymatches (Record1, Record2) VALUES ('11', '10')
GO
-- Organise the columns so that Record1 value is always < Record2 value
With cte_organise_columns
As
(Select A.Record1, A.Record2
From
( Select
CAST(Record1 AS bigint) As Record1,
CAST(Record2 AS bigint) As Record2
From dbo.fuzzymatches
Union All
Select Record1 = Record2, Record2 = Record1
From dbo.fuzzymatches
) A
Where A.Record1 <= A.Record2
),
-- Implement the recursive structure
cte_recursive_matches
As
(-- Define Anchor member records
Select Record1, Record2, GroupLevel = Record1
From cte_organise_columns
Union All
-- Define Recursive member referencing cte_recursive_matches
Select B.Record1, B.Record2, GroupLevel = A.Record1
From cte_organise_columns A
Inner Join cte_recursive_matches B On A.Record2 = B.GroupLevel
),
-- Only Get the Minimum value for the GroupLevel
cte_min_matches
As
(Select Record1, Record2, GroupLevel = MIN(GroupLevel)
From cte_recursive_matches
Group By Record1, Record2
)
-- Finally Select the results
Select A.Record1, A.Record2, B.GroupLevel
From dbo.fuzzymatches A
Inner Join cte_min_matches B On
(A.Record1 = B.Record1 and A.Record2 = B.Record2) OR
(A.Record1 = B.Record2 and A.Record2 = B.Record1)
Actual Result is:
Record1 Record2 GroupLevel
1 2 1
2 3 1
4 3 1
5 6 5
6 7 5
8 7 5
8 9 5
9 105
11 105
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply