Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
st99015207
Starting Member
2 Posts |
 Posted - 2010-03-24 : 09:03:15
|
| Hi,I have a table of ordered products. This table contains a column ('AttachTo') that can reference the unique ID ('ItemID') of itself. This is used to define a relationship between the two products. The table also has a incrementing integer column that is currently used as a sort by column to ensure that the products come out of any queries in the order that they were inserted. However I cant guarantee that they will be inserted in the correct order. Here is a cut down table example:SortOrder(INT), ItemID(GUID), AttachTo(GUID)1,{5A7F6324-34E6-4824-AB89-F35B6618124D},NULL2,{B5AA8ED7-932C-4C7C-AB99-EF5612AA4439},NULL3,{E29B29F8-0464-4298-86CB-130647DFB8FC},,{5A7F6324-34E6-4824-AB89-F35B6618124D}Essentially what I want to achieve is to have the any items with a non-null value in the 'AttachTo' column appear immediately after the row that they are related to, without relying on the current 'SortOrder' column that may or may not be accurate. Multiple items can be related to the primary row, ie. Multiple 'ItemID' values can appear in the 'AttachTo' column. I would like to perform this sort within the stored proc as opposed to at the presentation layer so that the results can natively be written to a grid without having to re-sort the data at runtime.Just to let you know, I have toyed with the idea of creating some form of composite sort column based upon a contatenation of the GUID values (which would of course be the same if the rows are related) - but had no joy. I also played with a self-join, but couldnt generate anything useful.Like all of these things, Im confident the solution is simple, and would appreciate a push in the right direction.Thanks for your time. Drop me a line if you need anymore info. |
|
|
|
|
|
|
|
Strange sort by problem