February 6, 2012 at 10:53 pm
is anyone able to convert this query using a WHILE LOOP? I do not want recursive CTE
DECLARE @tab1 TABLE
(Num1 int,
Num2 int
)
INSERT into @tab1 (Num1, Num2)
select 1 , 2
union all select 2,3
union all select 3,4
union all select 5,6
union all select 7,8
union all select 9,8;
-- Organise the columns so that Num1 value is always < Num2 value
With cte_organise_columns
As
(Select A.Num1, A.Num2
From
( Select
CAST(Num1 AS bigint) As Num1,
CAST(Num2 AS bigint) As Num2
From @Tab1
Union All
Select Num1 = Num2, Num2 = Num1
From @Tab1
) A
Where A.Num1 <= A.Num2
),
-- Implement the recursive structure
cte_recursive_matches
As
(-- Define Anchor member records
Select Num1, Num2, GroupLevel = Num1
From cte_organise_columns
Union All
-- Define Recursive member referencing cte_recursive_matches
Select B.Num1, B.Num2, GroupLevel = A.Num1
From cte_organise_columns A
Inner Join cte_recursive_matches B On A.Num2 = B.GroupLevel
),
-- Only Get the Minimum value for the GroupLevel
cte_min_matches
As
(Select Num1, Num2, GroupLevel = MIN(GroupLevel)
From cte_recursive_matches
Group By Num1, Num2
)
-- Finally Select the results
Select A.Num1, A.Num2, B.GroupLevel
From @Tab1 A
Inner Join cte_min_matches B On
(A.Num1 = B.Num1 and A.Num2 = B.Num2) OR
(A.Num1 = B.Num2 and A.Num2 = B.Num1)
February 7, 2012 at 8:03 am
why?
Recursive CTE's are pro:
http://msdn.microsoft.com/en-us/library/ms186243.aspx
February 7, 2012 at 8:33 am
Loundy (2/7/2012)
Recursive CTE's are pro:
http://msdn.microsoft.com/en-us/library/ms186243.aspx%5B/quote%5D
Hmmm. . . it is after all still a loop.
However, I'm not sure why you'd want to convert a recursive CTE into a while loop - unless to migrate code to a previous version of SQL Server?
February 7, 2012 at 8:51 am
Cadavre (2/7/2012)
Loundy (2/7/2012)
why?Recursive CTE's are pro:
http://msdn.microsoft.com/en-us/library/ms186243.aspx%5B/quote%5D
Hmmm. . . it is after all still a loop.
However, I'm not sure why you'd want to convert a recursive CTE into a while loop - unless to migrate code to a previous version of SQL Server?
True, but what other options are there apart from cursors / while loops? Self joins? 😀
February 7, 2012 at 3:49 pm
Recursive CTE's are not an issue. It works fine and gives expected result. But yes going back to previous version of SQL it will not work.
Definitely not interested in Cursor as we have a coding standard where we try not to implement cursors.
So While Loop is the way to go (thats what i think).
Unless someone has got any brighter idea !!!:-)
February 7, 2012 at 4:10 pm
rka (2/7/2012)
Definitely not interested in Cursor as we have a coding standard where we try not to implement cursors.So While Loop is the way to go (thats what i think).
And the difference between a while loop and a cursor is what?
If you're going to avoid cursors, avoid while loops too, they're just as bad.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 5:35 pm
The above code that I provided was just a sample code. In a real Prod environment, there will be a proper table with keys and indexes defined.
Also most of the code will be optimized when unit testing is carried out.
The objective was to build a Hierarchy Level, using various possibilities.
i.e. 1-->2, 2-->3 , therefore 1-->3
so if we have data like:
Num1 Num2 GroupLevel
1 2
2 3
4 5
we get GroupLevel for 1st and 2nd rows as 1, and GroupLevel for 3rd row as 4. The GroupLevel is always the minimum number of the hierarchy
And thanks everyone for the input. I managed to work out the code 😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply