March 24, 2010 at 10:03 am
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},NULL
2,{B5AA8ED7-932C-4C7C-AB99-EF5612AA4439},NULL
3,{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 identical '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.
From the simple example above, I would like to see:
SortOrder(INT), ItemID(GUID), AttachTo(GUID)
1,{5A7F6324-34E6-4824-AB89-F35B6618124D},NULL
3,{E29B29F8-0464-4298-86CB-130647DFB8FC},{5A7F6324-34E6-4824-AB89-F35B6618124D}
2,{B5AA8ED7-932C-4C7C-AB99-EF5612AA4439},NULL
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.
March 24, 2010 at 11:28 am
nick-604971 (3/24/2010)
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},NULL
2,{B5AA8ED7-932C-4C7C-AB99-EF5612AA4439},NULL
3,{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 identical '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.
From the simple example above, I would like to see:
SortOrder(INT), ItemID(GUID), AttachTo(GUID)
1,{5A7F6324-34E6-4824-AB89-F35B6618124D},NULL
3,{E29B29F8-0464-4298-86CB-130647DFB8FC},{5A7F6324-34E6-4824-AB89-F35B6618124D}
2,{B5AA8ED7-932C-4C7C-AB99-EF5612AA4439},NULL
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.
Try:
ORDER BY
coalesce(AttachTo,ItemID),
SortOrder
March 24, 2010 at 11:33 am
Like I said, I knew there was a simple solution 🙂
(I was certain I had tried that!!! Maybe the age is starting to show .....!)
Many thanks Lynn
March 24, 2010 at 11:41 am
Looking at it again, that will only work for a simple one level attach. If you start getting into a nested hierarchy like a multi-level BOM, it won't work.
March 25, 2010 at 3:37 am
Lynn,
There is one other requirement that I had totally forgot to include in the original question.
The order of the parent items should be by SortID as this will always be the most appropriate
order for returning results. Is this possible whilst still having the child items appear beneath their respective
owners ?
Thanks
Nick
March 25, 2010 at 6:23 am
nick-604971 (3/25/2010)
Lynn,There is one other requirement that I had totally forgot to include in the original question.
The order of the parent items should be by SortID as this will always be the most appropriate
order for returning results. Is this possible whilst still having the child items appear beneath their respective
owners ?
Thanks
Nick
It may require additional work. I need to confirm what version of SQL Server you are currently using. Based on the forum you have posted, I must assume that you are using at least SQL Server 2000. If you are using SQL Server 2005, I may be able to come up with a different solution easier, but I would say it could be done in SQL Server 2000.
March 25, 2010 at 6:26 am
Lynn,
We are using 2000, 2005 and 2008, so require compatibility down to 2000 level.
Many thanks
March 25, 2010 at 6:41 am
Thank you. I won't be able to spend much time on this at the moment, but hopefully someone else will look at the problem, or I will be able to work on it more tonight when I get home from work.
March 25, 2010 at 6:44 am
Lynn Pettis (3/25/2010)
Thank you. I won't be able to spend much time on this at the moment, but hopefully someone else will look at the problem, or I will be able to work on it more tonight when I get home from work.
Great, thanks.
March 25, 2010 at 6:47 am
Not sure how scalable this will be, but I did think of something as I was getting ready to leave for work:
create table #SortTest (
SortOrder int,
ItemID uniqueidentifier,
AttachTo uniqueidentifier null
)
;
insert into #SortTest
select 1, '5A7F6324-34E6-4824-AB89-F35B6618124D', null union all
select 2, 'B5AA8ED7-932C-4C7C-AB99-EF5612AA4439', null union all
select 3, 'E29B29F8-0464-4298-86CB-130647DFB8FC', '5A7F6324-34E6-4824-AB89-F35B6618124D'
;
select
st1.*
from
#SortTest st1
inner join #SortTest st2
on (st2.ItemID = coalesce(st1.AttachTo, st1.ItemID)
and st2.AttachTo is null)
order by
st2.SortOrder,
st1.SortOrder
;
drop table #SortTest;
March 25, 2010 at 6:54 am
And it may suffer from the same problem my original answer suffers, it may not work for a multi-level BOM.
March 25, 2010 at 7:19 am
Lynn,
That seems to work like a charm.
Thanks taking the time to produce a working example, its really appreciated.
I will do some real world testing on it shortly to see how it scales, but it will
only be used on fairly small rowsets so its looking good !
Nick
March 25, 2010 at 7:40 am
nick-604971 (3/25/2010)
Lynn,That seems to work like a charm.
Thanks taking the time to produce a working example, its really appreciated.
I will do some real world testing on it shortly to see how it scales, but it will
only be used on fairly small rowsets so its looking good !
Nick
Your welcome. For future questions, use what I posted as an example of how you should post data for others to use. You can find more by reading the first article I reference below in my signature block.
The only thing the article leaves out is also posting the expected results based on the sample data.
March 25, 2010 at 7:52 am
Whooops! :blush:
Thanks for the advice.
March 25, 2010 at 7:59 am
Not a problem, you appear to be new to SSC. The biggest reason to follwo the advice given, more people willing to jump in and help, plus you will get tested code in return.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply