autoexcrement (11/13/2013)
Can someone please help me to catch up? I feel I'm not understanding the issue. Using the OP's initial data:ITEM1 ITEM2 ID
0224180 0224181 1
0224180 0224190 1
0224181 0224180 1
0224181 0224190 1
0224190 0224180 1
0224190 0224181 1
0202294 0202295 2
0202295 0202294 2
0209250 0209251 3
0209251 0209250 3
Why, for example, does the last value pair "0209251, 0209250" get an ID of 3? (This value pair appears only 2 times in the data.) Or why does the value pair "0224190, 0224181" get an ID of 1? (This value pair appears twice in the data.)
Thanks, am hoping to grasp what's going on here so I can learn from it too! 🙂
I'll try to explain. Look at the last 2 rows containing 0209250 and 0209251. These share a relationship in that either product can be substituted for the other. The same is true for 0202295 and 0202294 (in the rows with ID = 2).
The first 6 rows all contain a pair chosen from any of these 3 products: 0224180, 0224181 and 0224190. Hence they share a relationship that any can be substituted for any other.
Therefore ID = Product Substitution Category (or something like that)
This is an excellent SQL problem to learn from as it is fiendishly diabolical to solve with high performance.
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