August 17, 2016 at 4:14 pm
And I got the back seat cover washed and returned without killing Ms Mya, the quietest, gentlest dog we have ever owned. 🙂 I just got through reading a posting by an old friend about his rumba picking up dog shit in the middle of the night and spreading it all over the house. He then tried to clean the rumba without taking the batteries out at oh dark 30 hours. It is a really funny story, but all really funny stories only happen to other people. But let us get back to topic
Logic is actually pretty straightforward, a row by row basis, if a row is a step number, not between the two to swapped steps, then leave it alone. If one of the rows supposed to go to the other position, then move everybody down (or up) and adjust the step numbers in the rows accordingly. Since updates are done to a table. "All at once" this is very quick and easy.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 18, 2016 at 8:40 am
DesNorton (8/17/2016)
Unless my understanding is incorrect, I believe that the movement in this solution is the wrong way around.
To prove this, let's take a look at only the first movement ... from_seq=5 to_seq=3.
The original seq is:
1, 2, 3, 4, 5, 6, ...
My understanding here is that we are moving [5] up the sequence to where [3] is currently, and [3] and [4] to each move down 1 space. Hence, I would expect the result to be:
1, 2, 5, 3, 4, 6, ...
However, the above code produces the following result, which is to move the [3] down to where [5] is currently, and [4] and [5] each move up 1 space:
1, 2, 4, 5, 3, 6, ...
Try sorting by the updated sequence rather than the original sequence.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2016 at 8:56 am
drew.allen (8/18/2016)
DesNorton (8/17/2016)
Unless my understanding is incorrect, I believe that the movement in this solution is the wrong way around.
To prove this, let's take a look at only the first movement ... from_seq=5 to_seq=3.
The original seq is:
1, 2, 3, 4, 5, 6, ...
My understanding here is that we are moving [5] up the sequence to where [3] is currently, and [3] and [4] to each move down 1 space. Hence, I would expect the result to be:
1, 2, 5, 3, 4, 6, ...
However, the above code produces the following result, which is to move the [3] down to where [5] is currently, and [4] and [5] each move up 1 space:
1, 2, 4, 5, 3, 6, ...
Try sorting by the updated sequence rather than the original sequence.
Drew
Oops. My bad :blush:
August 20, 2016 at 10:38 am
drew.allen (8/17/2016)
I realized this morning that my code needed a slight update. My updates sample data had the same ordering for the from_seq and the to_seq, but that is not necessarily the case. Here is the updated code to handle cases where the orderings on the two columns are different.
Correct me if I'm wrong but I don't think the code will work if you fully reorder the sequence i.e 1->10 etc.
😎
August 21, 2016 at 5:44 pm
How is this not just a simple update/cte?
WITH CTE AS
(
SELECT
seq
, ROW_NUMBER() OVER
(
ORDER BY seq, edited DESC
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq;
Having said that, I would prefer to not rely on an "edited" column being updated by the client as client times could be inaccurate/time zone dependent.
I would either replace that with a system maintained time stamp or have the new sequence requested in a separate column, such as "NewSequence".
WITH CTE AS
(
SELECT
seq
, NewSequence
, ROW_NUMBER() OVER
(
ORDER BY
COALESCE(NewSequence,seq)
, COALESCE(NewSequence,9999999)
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq, NewSequence = null;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 21, 2016 at 6:15 pm
mister.magoo (8/21/2016)
How is this not just a simple update/cte?
WITH CTE AS
(
SELECT
seq
, ROW_NUMBER() OVER
(
ORDER BY seq, edited DESC
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq;
Having said that, I would prefer to not rely on an "edited" column being updated by the client as client times could be inaccurate/time zone dependent.
I would either replace that with a system maintained time stamp or have the new sequence requested in a separate column, such as "NewSequence".
WITH CTE AS
(
SELECT
seq
, NewSequence
, ROW_NUMBER() OVER
(
ORDER BY
COALESCE(NewSequence,seq)
, COALESCE(NewSequence,9999999)
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq, NewSequence = null;
August 21, 2016 at 6:18 pm
Having said that, I would prefer to not rely on an "edited" column being updated by the client as client times could be inaccurate/time zone dependent.
I would either replace that with a system maintained time stamp or have the new sequence requested in a separate column, such as "NewSequence".
Agree. The Edited date is captured automatically when the user updates something. Thanks.
August 22, 2016 at 8:44 am
mister.magoo (8/21/2016)
How is this not just a simple update/cte?
WITH CTE AS
(
SELECT
seq
, ROW_NUMBER() OVER
(
ORDER BY seq, edited DESC
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq;
Having said that, I would prefer to not rely on an "edited" column being updated by the client as client times could be inaccurate/time zone dependent.
I would either replace that with a system maintained time stamp or have the new sequence requested in a separate column, such as "NewSequence".
WITH CTE AS
(
SELECT
seq
, NewSequence
, ROW_NUMBER() OVER
(
ORDER BY
COALESCE(NewSequence,seq)
, COALESCE(NewSequence,9999999)
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq, NewSequence = null;
Because it doesn't work when you want to move two sequences to consecutive places in the new sequence.
CREATE TABLE #temp (
seq INT,
edited DATETIME,
orig_ord VARCHAR(10),
desired_ord VARCHAR(10)
)
INSERT #temp(seq, edited, orig_ord, desired_ord)
VALUES
(1, '20160101', 'Step 1', 'Step 1'),
(2, '20160101', 'Step 2', 'Step 4'),
(3, '20160101', 'Step 3', 'Step 5'),
(4, '20160101', 'Step 4', 'Step 6'),
(5, '20160101', 'Step 5', 'Step 2'),
(6, '20160101', 'Step 6', 'Step 3'),
(7, '20160101', 'Step 7', 'Step 7')
;
UPDATE t
SET t.seq = v.new_seq, t.edited = GETDATE()
FROM #temp t
INNER JOIN (
SELECT old_seq, new_seq
FROM (
VALUES(5, 2), (6, 3)
) v(old_seq, new_seq)
) v
ON t.seq = v.old_seq
;
WITH CTE AS
(
SELECT
seq
, ROW_NUMBER() OVER
(
ORDER BY seq, edited DESC
) AS new_seq
FROM #temp
)
UPDATE cte
SET seq = new_seq;
SELECT *
FROM #temp t
ORDER BY t.seq
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 8:51 am
Drew, have you tested your code with a full reorder of the set? I haven't had the chance but somehow I'm thinking it will return the original order as the partition clause will cover the full set.
😎
August 22, 2016 at 8:31 pm
The task would become incredibly easy if you change seq data type to decimal(9,1)
Then you can place the moved step in between of any other steps, or in front of all steps, all behind of them all.
And after that -simply renumber with integer seq numbers.
_____________
Code for TallyGenerator
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply