Select a chain
-
Hi
I have a table with 2 ids in it
here is an example
ID FromID
1 0
2 1
3 0
4 2
5 3
6 4
and from these two fields i can create two chains of ids
1 - 2 - 4 - 6
and
3 - 5
I would like to write a function that would take an id and then return the last id in the chain, so if i passed 1, 2 or 4 for instance my function would return 6
I know i can do this with loops but I wondered if anyone had a better (cleaner) way?
Thanks in advance
-
erics44 (6/19/2012)
HiI have a table with 2 ids in it
here is an example
ID FromID
1 0
2 1
3 0
4 2
5 3
6 4
and from these two fields i can create two chains of ids
1 - 2 - 4 - 6
and
3 - 5
I would like to write a function that would take an id and then return the last id in the chain, so if i passed 1, 2 or 4 for instance my function would return 6
I know i can do this with loops but I wondered if anyone had a better (cleaner) way?
Thanks in advance
What if you pass 1 - 2 then also it should return 6(the last number in the chain) or 4??
-
🙂
very helpful thanks
i assume you dont understand?
-
Thats cool
Is all this your way of asking me to explain myself further?
-
DECLARE @t TABLE(ID INT, FromID INT)INSERT INTO @t(ID,FromID)
SELECT 1, 0 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 3 UNION ALL
SELECT 6, 4;
DECLARE @ID INT
SET @ID=1;
--SET @ID=2;
WITH CTE AS (
SELECT ID AS IDStart, ID
FROM @t
WHERE ID=@ID
UNION ALL
SELECT c.IDStart,t.ID
FROM @t t
INNER JOIN CTE C ON c.ID=t.FromID)
SELECT *
FROM CTE c
WHERE NOT EXISTS(SELECT * FROM @t t WHERE c.ID=t.FromID);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Or if you want your result chains laid out horizontally, you can do this:
DECLARE @t TABLE (ID INT, FromID INT)
INSERT INTO @t
SELECT 1, 0
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 0
UNION ALL SELECT 4, 2
UNION ALL SELECT 5, 3
UNION ALL SELECT 6, 4
;WITH Chain AS (
SELECT LinkID=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), ID
FROM @t WHERE FromID = 0
UNION ALL
SELECT LinkID, t.ID
FROM Chain c INNER JOIN @t t ON c.ID = t.FromID)
SELECT LinkID
,Link=STUFF(
(SELECT '-' + CAST(ID AS VARCHAR)
FROM Chain c2
WHERE c1.LinkID = c2.LinkID
FOR XML PATH(''), root('MyString'), type).value('/MyString[1]','varchar(max)' )
, 1, 1, '')
FROM Chain c1
GROUP BY LinkID
Results:
LinkIDLink
11-2-4-6
23-5
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Thanks a lot
2 cracking bits of code
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply