Problem: how to realize custom order

Question

Problem: how to realize custom order

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Gianluca Sartori (3/25/2011)
Yes, now translate it into T-SQL, set-based if possible. 🙂 Ugh!

One made-to-order, set-based solution, utilizing the pseudo-cursor (aka Quirky Update):

(please READ AND UNDERSTAND the article mentioned below before implementing, and decide if you're willing to live with an undocumented solution for this!)

DECLARE @test TABLE (

customerId INTEGER,

routeCode INTEGER,

routePos INTEGER,

orderPos INTEGER NULL, -- Note the new column to hold the position for ordering

PRIMARY KEY CLUSTERED (routeCode, routePos) ); --<< NOTE!!! IMPORTANT!!!

INSERT INTO @test (customerId, routeCode, routePos)

SELECT 2783367, 1, 1 UNION ALL

SELECT 2783563, 1, 3 UNION ALL

SELECT 2783591, 1, 4 UNION ALL

SELECT 2783570, 1, 5 UNION ALL

SELECT 2783562, 1, 6 UNION ALL

SELECT 2783475, 1, 7 UNION ALL

SELECT 2783491, 2, 1 UNION ALL

SELECT 2783492, 2, 2 UNION ALL

SELECT 2783493, 2, 3 UNION ALL

SELECT 2783495, 2, 4 UNION ALL

SELECT 2783494, 3, 1 UNION ALL

SELECT 2783497, 3, 2 UNION ALL

SELECT 2783139, 3, 3 UNION ALL

SELECT 2783376, 3, 4 UNION ALL

SELECT 2783499, 3, 5 UNION ALL

SELECT 2783500, 3, 6 UNION ALL

SELECT 2783511, 4, 1 UNION ALL

SELECT 2783614, 4, 3 UNION ALL

SELECT 2783654, 4, 4 UNION ALL

SELECT 2783616, 4, 6 UNION ALL

SELECT 2783595, 5, 1 UNION ALL

SELECT 2783687, 5, 2 UNION ALL

SELECT 2783681, 5, 3 UNION ALL

SELECT 2783678, 5, 4 UNION ALL

SELECT 2783634, 5, 6 UNION ALL

SELECT 2783621, 5, 7 UNION ALL

SELECT 2783657, 6, 1 UNION ALL

SELECT 2783679, 6, 2 UNION ALL

SELECT 2783671, 6, 3 UNION ALL

SELECT 2783655, 6, 4 UNION ALL

SELECT 2783689, 6, 6 UNION ALL

SELECT 2783593, 6, 8 UNION ALL

SELECT 2783663, 7, 1 UNION ALL

SELECT 2783686, 7, 3 UNION ALL

SELECT 2783685, 7, 4 UNION ALL

SELECT 2783615, 7, 5;

-- declare and initialize variables needed for the update statement.

DECLARE @routeCode INT, -- Lead column in the PK, for anchor column

@oddPos INT,

@evenPos INT,

@orderPos INT,

@UseOdd BIT,

@Sequence INT; -- for safety check

SET @Sequence = 0;

SET @oddPos = 0;

SET @evenPos = 0;

SET @UseOdd = 1;

/*

This form of the UPDATE statement has some rules for proper usage.

See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

for a complete discussion of how this works, and all of the rules for utilizing it.

>>>>>> If you don't follow ALL the rules, you WILL mess up your data!!! <<<<<<

Also, see http://www.sqlservercentral.com/Forums/FindPost980118.aspx

and http://www.sqlservercentral.com/Forums/FindPost981258.aspx for the logic

behind the safety check process.

*/

WITH SafeTable AS

(

-- generate table with a sequence column in clustered index order

-- in order to verify that update is happening in the correct order

SELECT *,

Sequence = ROW_NUMBER() OVER (ORDER BY routeCode, routePos)

FROM @test

)

--SELECT * FROM SafeTable

UPDATE t

-- Verify in proper sequence order; if not, throw an error so nothing is updated

-- Safety check on variable being carried over to the next row is to prevent

-- rows from being updated in the wrong order.

SET @UseOdd = CASE WHEN @routeCode IS NULL

THEN 1 -- first record, start with odd

WHEN @routeCode = routeCode

THEN @UseOdd -- continue with current

-- after this, there is a new

-- routeCode, so we need to

-- evaluate whether to use

-- odd or even next.

WHEN @oddPos+1 = @evenPos

THEN 1 -- both routeCodes end at same time

WHEN @oddPos > @evenPos

THEN 0 -- more odds so switch to even

WHEN @UseOdd = 0

THEN 1 -- switch from even to odd

ELSE 0 -- switch to even

END,

@orderPos = OrderPos = CASE WHEN Sequence = @Sequence + 1 THEN

CASE WHEN @routeCode IS NULL

THEN @oddPos + 1

WHEN @UseOdd = 1 THEN @oddPos + 2

ELSE @evenPos + 2

END

ELSE 1/0 END, -- not in proper sequence order

-- throw an error

@oddPos = CASE WHEN @UseOdd = 1 THEN @orderPos

ELSE @oddPos

END,

@evenPos = CASE WHEN @UseOdd = 0 THEN @orderPos

ELSE @evenPos

END,

@Sequence = @Sequence + 1, -- update safety check column

@routeCode = routeCode -- anchor column (first column in clustered index)

FROM SafeTable t WITH (TABLOCKX) -- lock table to prevent changes by others

OPTION (MAXDOP 1); -- prevent parallelism!

SELECT * FROM @test

ORDER BY orderPos;

Edit: added quote. (couldn't resist! :-D)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

@Birby1974,

Did Wayne's solution work for you or what?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Birby1974 Old Hand Points: 327 More actions · Answer 3

Guys, thankyou very much for all your answers.

I'm reading all your posts in this moment, because I just got back from a romantic weekend !!

🙂

WayneS SSC Guru Points: 95461 More actions · Answer 4

Birby1974 (3/27/2011)
Guys, thankyou very much for all your answers.
I'm reading all your posts in this moment, because I just got back from a romantic weekend !!
🙂

If you're having a romantic weekend, I sure hope that they understand why you're on here then!!! :-P;-)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Birby1974 Old Hand Points: 327 More actions · Answer 5

WayneS, I'll try your solution Tuesday and I'll let you know.

Thanks!

Birby1974

Birby1974 Old Hand Points: 327 More actions · Answer 6

WayneS, your solution works fine for me 🙂

Thanks again.

Birby1974

WayneS SSC Guru Points: 95461 More actions · Answer 7

Birby1974 (3/29/2011)
WayneS, your solution works fine for me 🙂
Thanks again.
Birby1974

Great, thanks for the feedback.

Now, please read the first line in my signature, and ask if you have any questions about it. This is a method that a lot of folks don't understand, and it is important (at least to the data ==>> your job) that it be understood to do it right.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

WayneS (3/25/2011)
Gianluca Sartori (3/25/2011)
Yes, now translate it into T-SQL, set-based if possible. 🙂 Ugh!
One made-to-order, set-based solution, utilizing the pseudo-cursor (aka Quirky Update):
(please READ AND UNDERSTAND the article mentioned below before implementing, and decide if you're willing to live with an undocumented solution for this!)
DECLARE @test TABLE (
customerId INTEGER,
routeCode INTEGER,
routePos INTEGER,
orderPos INTEGER NULL, -- Note the new column to hold the position for ordering
PRIMARY KEY CLUSTERED (routeCode, routePos) ); --<< NOTE!!! IMPORTANT!!!
INSERT INTO @test (customerId, routeCode, routePos)
SELECT 2783367, 1, 1 UNION ALL
SELECT 2783563, 1, 3 UNION ALL
SELECT 2783591, 1, 4 UNION ALL
SELECT 2783570, 1, 5 UNION ALL
SELECT 2783562, 1, 6 UNION ALL
SELECT 2783475, 1, 7 UNION ALL
SELECT 2783491, 2, 1 UNION ALL
SELECT 2783492, 2, 2 UNION ALL
SELECT 2783493, 2, 3 UNION ALL
SELECT 2783495, 2, 4 UNION ALL
SELECT 2783494, 3, 1 UNION ALL
SELECT 2783497, 3, 2 UNION ALL
SELECT 2783139, 3, 3 UNION ALL
SELECT 2783376, 3, 4 UNION ALL
SELECT 2783499, 3, 5 UNION ALL
SELECT 2783500, 3, 6 UNION ALL
SELECT 2783511, 4, 1 UNION ALL
SELECT 2783614, 4, 3 UNION ALL
SELECT 2783654, 4, 4 UNION ALL
SELECT 2783616, 4, 6 UNION ALL
SELECT 2783595, 5, 1 UNION ALL
SELECT 2783687, 5, 2 UNION ALL
SELECT 2783681, 5, 3 UNION ALL
SELECT 2783678, 5, 4 UNION ALL
SELECT 2783634, 5, 6 UNION ALL
SELECT 2783621, 5, 7 UNION ALL
SELECT 2783657, 6, 1 UNION ALL
SELECT 2783679, 6, 2 UNION ALL
SELECT 2783671, 6, 3 UNION ALL
SELECT 2783655, 6, 4 UNION ALL
SELECT 2783689, 6, 6 UNION ALL
SELECT 2783593, 6, 8 UNION ALL
SELECT 2783663, 7, 1 UNION ALL
SELECT 2783686, 7, 3 UNION ALL
SELECT 2783685, 7, 4 UNION ALL
SELECT 2783615, 7, 5;
-- declare and initialize variables needed for the update statement.
DECLARE @routeCode INT, -- Lead column in the PK, for anchor column
@oddPos INT,
@evenPos INT,
@orderPos INT,
@UseOdd BIT,
@Sequence INT; -- for safety check
SET @Sequence = 0;
SET @oddPos = 0;
SET @evenPos = 0;
SET @UseOdd = 1;
/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
>>>>>> If you don't follow ALL the rules, you WILL mess up your data!!! <<<<<<
Also, see http://www.sqlservercentral.com/Forums/FindPost980118.aspx
and http://www.sqlservercentral.com/Forums/FindPost981258.aspx for the logic
behind the safety check process.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT *,
Sequence = ROW_NUMBER() OVER (ORDER BY routeCode, routePos)
FROM @test
)
--SELECT * FROM SafeTable
UPDATE t
-- Verify in proper sequence order; if not, throw an error so nothing is updated
-- Safety check on variable being carried over to the next row is to prevent
-- rows from being updated in the wrong order.
SET @UseOdd = CASE WHEN @routeCode IS NULL
THEN 1 -- first record, start with odd
WHEN @routeCode = routeCode
THEN @UseOdd -- continue with current
-- after this, there is a new
-- routeCode, so we need to
-- evaluate whether to use
-- odd or even next.
WHEN @oddPos+1 = @evenPos
THEN 1 -- both routeCodes end at same time
WHEN @oddPos > @evenPos
THEN 0 -- more odds so switch to even
WHEN @UseOdd = 0
THEN 1 -- switch from even to odd
ELSE 0 -- switch to even
END,
@orderPos = OrderPos = CASE WHEN Sequence = @Sequence + 1 THEN
CASE WHEN @routeCode IS NULL
THEN @oddPos + 1
WHEN @UseOdd = 1 THEN @oddPos + 2
ELSE @evenPos + 2
END
ELSE 1/0 END, -- not in proper sequence order
-- throw an error
@oddPos = CASE WHEN @UseOdd = 1 THEN @orderPos
ELSE @oddPos
END,
@evenPos = CASE WHEN @UseOdd = 0 THEN @orderPos
ELSE @evenPos
END,
@Sequence = @Sequence + 1, -- update safety check column
@routeCode = routeCode -- anchor column (first column in clustered index)
FROM SafeTable t WITH (TABLOCKX) -- lock table to prevent changes by others
OPTION (MAXDOP 1); -- prevent parallelism!
SELECT * FROM @test
ORDER BY orderPos;
Edit: added quote. (couldn't resist! :-D)

Well done. And I love that quote.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)