March 26, 2013 at 1:02 am
Hi,
Been stuck on this for a little while and hoping that someone can help me figure out my logic problems.
So my main issue is when i want to change the ordering and move and earlier number to a later number (Think numbers 1 to 8 and i want to move number 2 to the number 6 position)
The other side works (moving 6 to 2).
The initial column is only there so i can verify my code - it does not exist in the real table.
There is only one order column in the real table.
The changed column is where im hoping to see the correct reordering done.
As this is required to work over multiple tables im hoping it would be suitable to convert into an iTVF or something similar.
Some guidance on how to do it and then use it would be great.
IF OBJECT_ID('TempDB..#temp13','U') IS NOT NULL
drop table #temp13
select *
into #temp13
from (
select 1 "changed", 1 "initial" union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6 union all
select 7,7 union all
select 8,8
) b
declare @startNum as int = 1
declare @oldnum as int = 3
if @startNum < @oldnum
begin
update #temp13
set changed = -1
from #temp13
where changed = @oldnum
update #temp13
set changed = changed+1
from #temp13
cross join Tally
where N >= @startNum and N = changed and N <= @oldnum
update #temp13
set changed = @startNum
from #temp13
where changed = -1
end
else
begin
--unsure what needs to go here
--tried multiple things but none have worked so far
end
select *
from #temp13
Expected output where @startNum < @oldnum (startnum 2 oldnum 4)
changedinitial
11
32
43
24
55
66
77
88
Expected output where @startNum > @oldnum (startnum 4 oldnum 2)
changedinitial
11
42
33
24
55
66
77
88
So after shuffling the numbers around i would also need to ensure that they are numbered 1 to x.
If something isnt clear or more info is required please let me know and i will do my best.
March 26, 2013 at 2:18 am
matak (3/26/2013)
Expected output where @startNum < @oldnum (startnum 2 oldnum 4)changedinitial
11
32
43
24
55
66
77
88
3 rows affected
Expected output where @startNum > @oldnum (startnum 4 oldnum 2)
changedinitial
11
42
33
24
55
66
77
88
Two rows affected
Are these both correct? The second sample is consistent with your explanation, but I can't make sense of the first example.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2013 at 2:23 am
What you're doing is pointless. The physical order of the table is not something you can use anyway. Have a read of this --> http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx%5B/url%5D.
March 26, 2013 at 2:30 am
Cab you please be more precise on the logic behind the ordering.....If you simply want to
move and earlier number to a later number
then there can be a no. of possibilities......What is the logic by which you decide a certain order??....
You did show us some code.....but i guess that does not work since you posted for help here....so lets start from scratch....please tell us the logic that gets you a particular order.......
March 26, 2013 at 2:51 am
Sorry i will try to clarify. I have two different situations that i can think of.
The first is when im trying to change the order of a current item to an earlier number.
@startNum < @oldnum --here @startnum is the new "order" that the @oldnum will receive.
So i have something like this
orderId col1 col2 col3
1 data1 data1 data1
2 data2 data2 data2
3 data3 data3 data3
4 data4 data4 data4
5 data5 data5 data5
And i need to change the ordering of the data (its in a .net app and instead of having the app call the database thousands of times i was hoping to do a single update in the db).
So my first situation is where i need to have the row defined by orderId = 4 be moved to the second position (@startNum < @oldnum) like below
neworderidoldorderIdcol1col2col3
1 1 data1data1data1
2 4 data4data4data4
3 2 data2data2data2
4 3 data3data3data3
5 5 data5data5data5
The second sample needs to move "down the list" (@startNum > @oldnum) so the orderId = 2 needs to be moved down the the orderId = 4 position
neworderidoldorderIdcol1col2col3
1 1 data1data1data1
2 3 data3data3data3
3 4 data4data4data4
4 2 data2data2data2
5 5 data5data5data5
I appreciate you taking the time to look at this - im just having a slight problem articulating what i want to accomplish.
If it still isnt clear i will try to think of another way i can explain the issue.
March 26, 2013 at 2:55 am
Cadavre, Im not expecting to have the physical data ordered.
For any queries that will work on this table it will always have an
order by orderId asc
I guess its like a set of instructions the users create and they need to be displayed in the correct order.
Sometimes they get the order wrong and it needs to get fixed.
March 26, 2013 at 2:58 am
Vinu, part of this works but im pretty sure its not a good way of doing it and it will need to be performed over a number of tables which is why i have my fingers crossed it can be converted to an iTVF once the logic is there.
Its the code in the else statement that im having difficulties with.
March 26, 2013 at 4:36 am
Is this what you're looking for?
DROP TABLE #SampleData
;WITH SampleData AS (
SELECT *
FROM (VALUES ('A',1), ('B',2), ('C',3), ('D',4), ('E',5), ('F',6)) d ([SomeStuff], [OrderlineID])
)
SELECT *
INTO #SampleData
FROM SampleData
-- check
SELECT * FROM #SampleData ORDER BY OrderlineID
-- Move OrderlineID 2 to between OrderlineID 4 and OrderlineID 5 i.e. row 4
UPDATE #SampleData SET
OrderlineID = CASE
WHEN OrderlineID = 2 THEN 4 -- the row subject to the move
ELSE OrderlineID - 1
END
WHERE OrderlineID BETWEEN 2 AND 4
-- check
SELECT * FROM #SampleData ORDER BY OrderlineID
-- Reverse the change:
-- Move OrderlineID 4 to between OrderlineID 1 and OrderlineID 2 i.e. row 2
UPDATE #SampleData SET
OrderlineID = CASE
WHEN OrderlineID = 4 THEN 2 -- the row subject to the move
ELSE OrderlineID + 1
END
WHERE OrderlineID BETWEEN 2 AND 4
-- check
SELECT * FROM #SampleData ORDER BY OrderlineID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 26, 2013 at 7:20 pm
Thanks Chris, much appreciated - this looks like what im after.
Seeing how easy the code really is it looks like i was over complicating it.
Is this something that could be converted to an iTVF ?
I have done a bit of reading on them but have zero experience so far.
March 27, 2013 at 3:44 am
You're welcome.
Taking it one step further (combining both queries into one, using variables for the current and new row id's), you might write something like this:
DECLARE @OldPos INT, @NewPos INT
-- Move OrderlineID 2 to between OrderlineID 4 and OrderlineID 5 i.e. row 4
SELECT @OldPos = 2, @NewPos = 4
UPDATE #SampleData SET
OrderlineID = CASE
WHEN OrderlineID = @OldPos THEN @NewPos -- the row subject to the move
WHEN @NewPos > @OldPos THEN OrderlineID - 1
WHEN @NewPos < @OldPos THEN OrderlineID + 1
END
WHERE OrderlineID BETWEEN
CASE WHEN @OldPos < @NewPos THEN @OldPos ELSE @NewPos END
AND
CASE WHEN @OldPos < @NewPos THEN @NewPos ELSE @OldPos END
The WHERE clause ensures that only rows to be updated are touched, and the CASE stuff in the WHERE clause ensures that the range lo and hi are in the right order. It's packaged as a unit of work which fits the purpose of a stored procedure nicely.
You could use an iTVF to calculate old and new OrderlineID values but it doesn't really offer enough to be worthwhile in this case. It might look something like this, unwrapped into a CROSS APPLY block:
SELECT @OldPos = 4, @NewPos = 2
UPDATE s SET
OrderlineID = x.NewOrderlineID
FROM #SampleData s
CROSS APPLY ( -- this could be an iTVF taking parameters s.OrderlineID, @OldPos, @NewPos
SELECT
si.OrderlineID,
NewOrderlineID = CASE
WHEN s.OrderlineID = @OldPos THEN @NewPos
WHEN @NewPos > @OldPos THEN si.OrderlineID - 1
WHEN @NewPos < @OldPos THEN si.OrderlineID + 1
END
FROM #SampleData si
WHERE si.OrderlineID = s.OrderlineID -- outer reference
AND si.OrderlineID BETWEEN
CASE WHEN @OldPos < @NewPos THEN @OldPos ELSE @NewPos END
AND
CASE WHEN @OldPos < @NewPos THEN @NewPos ELSE @OldPos END
) x
Notice that you're now reading the target table twice. Not only that, SQL Server may have to copy out the rows to be affected as a static working set otherwise there's a danger of updating the same row twice (Halloween protection), which you can see in the actual plan for the update if you omit the clustered index on the temp table. The net result is likely to be a far more expensive process than the simple stored procedure.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2013 at 6:55 pm
Thanks for that chris.
I guess i was hoping i could create some sort of function that i could apply to multiple tables.
Looks like that wont be happening.
Still its a good read and another example i can learnt on and hopefully create my own one day.
March 28, 2013 at 2:17 am
It's not really appropriate for a function, but a stored procedure could do the job - taking tablename, starting position and ending position as parameters. It would involve dynamic SQL. I guess you have to ask yourself if you are likely to apply this process to a large enough number of tables to make the exercise worthwhile. If you are, then post back for some assistance on how to do the conversion to dynamic SQL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 29, 2013 at 4:58 am
Yep i understand after seeing the function that its not appropriate.
There is only around 5 tables so far where this needs to be done so its manageable in separate stored procs for now.
Im pretty comfortable with dynamic sql - unfortunately due to various reasons its used quite a lot where im currently working.
Thanks for all the help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply