Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: SQL Gurus - SQL Query Help

    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 or a Stored Procedure which can achieve this result?

    Trick is that not always Column1 will be Less than Column2

  2. #2
    Join Date
    Jul 2011
    Posts
    4
    Finally figured this out using recursive CTE

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Care to share your code with us? Many people will be curious about your solution.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2011
    Posts
    4
    The full solution is:

    Code:
    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)

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Nice solution.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •