I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 )

The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160.

I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

Any help would be greatly appreciated. If I need to make anything clearer, please let me know.

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.

What I'm really trying to achieve is, a user inputs a part number, ie STC8572 (the customer does not necessarily have access to or know any of the other part numbers associated with this number), the lookup table then checks for supersessions of that part number - in this case:

The query then returns the newest number as the number to be used, along with the part information, which it knows based on the 'source' table, with a message such as 'The part number you have entered has been superseded to SFP500160'.

The query will also return all of the previous numbers associated with that number, to be consumed by the application for cross reference.

Is the above exactly the way you want to see the output from the query? In other words, do you want a single row returned as your results? Are the part numbers in one column or more than one? If the latter, how many supersessions are possible?

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.

Baring in mind,that we are starting the search with only one of these part numbers known to the end user.

The part supersession trail length can be quite long, I think the longest so far is 177. They average 5-10 in most instances though.

The way the supersessions table is provided to me is a reason for my headache. Usually, you would expect the search to filter "old part number > new part number > old part number > new part number" and so forth but in this instance, the later part number can be in either the "OriginalPartNumber" column or the "NewPartNumber" column. With more than 70,000 rows and a monthly update of the Supersessions table in this format, it would be nice to have a clean and easy way to find the part number supersession trail.

There are several key issues here:1. The need to eliminate a PN from the chain when it's already been resolved, hence I switched from the recursive CTE to the WHILE LOOP with EXCEPT.2. At a particular level (and all of your PNs just below STC9191 fall into this category), I am not sure of the sort criteria. Perhaps you've got another column (like a date the PN was introduced) to help here.3. It was not real clear from your initial description whether your Supersessions table lists the prior PN in the second column or whether those columns are reversed. I called that column PNPrior based on my assumption.Edit: Oh yeah, and one more thing.4. I'm not clear if you're trying to resolve a PN passed in by the UI (like @PartNo) or whether you always want to resolve from the Source table. I think I may have gotten that part wrong. But you should be able to work around that by using @PartNo in a WHERE clause of the first SELECT INTO #Hierarchy (instead of the JOIN).

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.

The supersessions chain appears to have no logic to the old and new part number columns. Old and new numbers are mixed up in both. Unfortunately, I only have two columns worth of data, there is no time period logged for the part number change. Largely, because I suppose it's mostly irrelevant to the end user.

The important thing, is that a list of previous part numbers is returned, regardless of order, for a point of reference.

And yes, I will be pushing data from the UI in to the SELECT statement - purely the one part number that they are searching for though.

For instance, if I searched for STC3333 in your example, it would redirect me to SFP500160 and then provide the UI with an array of previous part numbers linked to that latest number, as well as information from the [source] table for SFP500160.

The more I think about it, the more of a nightmare I can see it becoming, with no point of reference on the supersession columns ...

Can I send you the complete table, which I can export as either a CSV or TAB delimited file?

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.

p.barwick (2/3/2014)Color me stupid but where exactly do I need to declare it?

Thanks.

It is already declared in the code I provided. You need to do it before the first SELECT into the temp #Hierarchy table. Let's do this a step at a time (new solution that may be closer to what you need):

That whole sorting thing is pretty problematic. I suspect you won't like the ordering if you happen to pick a PN that's somewhere in the middle of the chain. Like I said, without a date of supersession or something, driving the ordering is going to be a headache.

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.