Nested values in a table
-
Not too sure if the subject of this post accurately reflects the nature of the question, but it is worth a shot:
I have a table with 2 columns, Old and New.
For example, the old and new columns are populated so:
old new
--- ---
x y
y z
a b
I am struggling to write a query which would return me the latest new value for an input of an old one.
Example: If I input 'x', it should return me 'z' and if i input 'z' it should return me 'z' as well. Along the same token, if i input 'y', i should get 'z' and for 'a' should get 'b'.
I was thinking whether I would need to use recursion or not?
Any help would be appreciated.
Thanks.
-
How do you define 'latest'? Do you have a date or timestamp somewhere in there or some other way to order the records?
If the table has
y a
x a
x y
a c
y z
z x
what should it return? for x? for y? for a?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
There is no timestamp, unfortunately. Just the fact that the column name is New and Old defines the latest value. For your example, im afraid that is not a possible case since i am dealing with unique values for the old value. so the column old would infact be a key column(no duplicates)
But again, for your example, x and y are a bad case but fir a it would return c.
hope that makes sense.
-
Ok, a bit better.
so...
z a
x z
y x
if I ask x, I'll get a
if I ask y, I'll also get a
Is that right?
Is there any limit on how deep this nesting can go?
Are you using sql 2000, or sql 2005? (very easy in the latter with CTEs)
In SQL 2000, it'll probably be necessary to define the max possible depth.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Spot on there. That is exactly what I want.
I am using SQL 2000. On the limit, hard to say, but shouldn't go more than 30 times.
-
Hmmm, that's going to be one messy query.
Let me think about this....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I think this will work. I've only done it 3 levels deep, but the principle's there and you should be able to extend it to as many levels as you need.
DECLARE
@StartValue char(1)
SET @StartValue = 'x'
SELECT
DISTINCT COALESCE(Level3.NewValue, Level2.NewValue,Level1.NewValue,Nested.NewValue) FinalValue
FROM Nested
LEFT JOIN Nested Level1 ON Nested.NewValue=Level1.OldValue
LEFT JOIN Nested Level2 ON Level1.NewValue=Level2.OldValue
LEFT JOIN Nested Level3 ON Level2.NewValue=Level3.OldValue
WHERE Nested.OldValue = @StartValue
OR COALESCE(Level3.NewValue, Level2.NewValue, Level1.NewValue, Nested.NewValue) = @StartValue
My test table was as follows:
z a
x z
y x
b c
c d
a p
d e
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hmm...looks interesting. Join the table with itself.
Yeah, seems viable for this instance. Will check it out and see how it goes.
Thanks for your help mate.
Cheers.
-
Yes, and you have to join it as meny times as there are levels in the nesting.
Just a warning. It's not going to run fast. I'd recomend that you index both columns of the table.
For faster returns you can do the following, but then it won't pick up cases where the value passed is only in the new column
DECLARE
@StartValue char(1)
SET @StartValue = 'x'
SELECT
COALESCE(Level3.NewValue, Level2.NewValue,Level1.NewValue,Nested.NewValue) FinalValue
FROM Nested
LEFT JOIN Nested Level1 ON Nested.NewValue=Level1.OldValue
LEFT JOIN Nested Level2 ON Level1.NewValue=Level2.OldValue
LEFT JOIN Nested Level3 ON Level2.NewValue=Level3.OldValue
WHERE Nested.OldValue = @StartValue
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yeah, I was just thinking about the speed of the query. With so many joins, it would definitely affect the performance. But yeah, indexing should limit the damage.
When the value is only in the new column, guess will hv to do some sort of a count(1) where oldvalue = 'x', and can then decide whether to do the join, or not.
Should be interesting.
-
It's better to do it in function which will use a loop to reach the end of that chain.
Otherwise you can never guarantee that you are using enough of joins.
_____________
Code for TallyGenerator -
Have you ever thought what would happen if someone put a clustered index, then all ur assumption is blown away. This is very tricky cos u don't know which one is the "Current Record" and your current is relative.
Thanks
Sreejith
-
Try something like this:
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'Find_Newest')
DROP FUNCTION Find_Newest
GO
CREATE FUNCTION dbo.Find_Newest
(@OldOne nvarchar(20) )
RETURNS nvarchar(20)
AS
BEGIN
Declare @N nvarchar(20), @O nvarchar(20)
Declare @Time datetime
select @N = O.New
FROM YourTable O
WHERE O.Old = @OldOne
IF @@Rowcount = 0
RETURN @OldOne
WHILE @N IS NOT NULL
BEGIN
SET @O = @N
SET @N = (select New
FROM YourTable
WHERE Old = @N and New <> Old) -- in case chains are terminated by repeating old value
END
RETURN @O
END
GO
CREATE TABLE YourTable (
Old nvarchar(20),
New nvarchar(20)
 
INSERT INTO YourTable (Old, New)
SELECT 'a', 'b'
union
select 'x', 'y'
union
SELECT 'y', 'z'
GO
SELECT dbo.Find_Newest ('a'),
dbo.Find_Newest ('b'),
dbo.Find_Newest ('x'),
dbo.Find_Newest ('y'),
dbo.Find_Newest ('z')
GO
DROP TABLE YourTable
But be careful: cycles in your references may cause endless looping.
If it's possible you may need to use Table variable and check if this value has already appeared.
_____________
Code for TallyGenerator -
I hadn't thought about using a function to loop n times. But that is a good thought actually.
And it may be faster as well. Actually, I think it will definitely be faster.
I am still thinking if the structure of the table can be altered. That would help in the end. Like maybe putting a timestamp (which would make the whole thing extremely easy).
Trouble is getting permission to change the structure.
But the function is what I am going with for the moment. Lets see how the performance is.
Thanks
-
Sure you do... "Old" = Parent, "New" = Child. It's a classic hierarchical problem. There has to be a dozen good articles and lot's of good posts on this forum on how to solve that problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply