Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Custom Sequence Numbering Expand / Collapse
Author
Message
Posted Monday, February 7, 2011 9:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:08 PM
Points: 1,945, Visits: 2,941
This weekend, I found some old floppy disks with old SQL puzzles and columns i had written 10+ years ago.
The original version of this problem was based on parking spaces. Given a motorpool with numbered parking spaces, you want to move the automobiles around.

CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);

Re-arrange the display order based on the parking_space column:

CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER, @new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space
OR parking_space BETWEEN @new_parking_space AND @old_parking_space;

When you want to drop a few rows, remember to close the gaps with this:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);

Back then, we did not have ROW_NUMBER(), so today, we might use:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT ROW_NUMBER() OVER(ORDER BY M1.parking_space)
FROM Motorpool AS M1);

Likewise, we did not have the MERGE statement and many programmers used the proprietary Sybase UPDATE..FROM.. syntax in spite of its problems. Today, we would use ANSI/ISO Standard code for what you posted:

MERGE INTO Task_List
USING (SELECT ROW_NUMBER()
OVER (PARTITION BY person_id
ORDER BY person_id, task_id) - 1
AS new_task_seq,
task_id, person_id
FROM Task_List) AS N_SEQ
ON Task_List.task_id = N_SEQ.task_id
WHEN MATCHED
THEN UPDATE
SET task_seq = N_SEQ.new_task_seq;



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
Post #1059646
Posted Monday, February 7, 2011 9:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

Good looking proc though. Nice to see classy SQL.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059670
Posted Monday, February 7, 2011 9:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based. Your proc works fine but you won't end up with continuous sequence numbers starting at zero. If you had say thirty items in the list and you randomly deleted fifteen, you would still have gaps between your sequence numbers (as you may have deleted all the rows sequenced between 5 and 15). Your proc would indeed get the sequencing correct but still with the gaps in the numbers, whereas mine would remove the gaps. Basically, mine takes the actual row count into effect, so you get back a sequence number range equal to zero to the row count -1. Admitedly this may be a bit over the top but I hate gaps in sequence numbers and know these will occur due to deletes. So I don't just act on the existing sequence numbers, I do the required move and then adjust them to be continuous.

Good looking proc though. Nice to see classy SQL.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059674
Posted Monday, February 7, 2011 9:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
Blimey, well, it proves that old problems never actually go away but the code to deal with them just evolves.

Current reading up on the Merge command.... Thank you for pointing me at this. I'm never afraid to be shown a better solution; indeed, if this never happenned then I'd never learn anything!

Cheers, James



James
MCM [@TheSQLPimp]
Post #1059687
Posted Monday, February 7, 2011 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:53 PM
Points: 2, Visits: 59
I have implemented a solution very similar to the one in Ten Centuries post for a similar problem. I think it is simpler, more elegant and easier to read than your solution. Please don’t read that as insult as I think that your solution is more robust for the reason that you described and can understand the corresponding increased complexity.

However, it would probably be beneficial to anyone reading if you had included a description of that added functionality in the original post. Also, as you have written your proc, there is no way to take advantage of that functionality so the reason you are doing it the way you are can’t really be capitalized upon. The setTodoSequence proc only provides a way to change the sequence, not delete a block of items, so there is no reason to have that stored procedure be more complicated than a simpler update statement with the appropriate condition and range testing.

I do think there is good information here and I have benefitted from seeing row_number() used in this way.
Post #1059760
Posted Monday, February 7, 2011 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
Thank you for the kind comments, they are all very useful. This is the first article I have tried to write and judging by the feedback, I'm on the right track. Believe me, I take nothing as an insult, it's all constructive! I can see by reading the article back how I could have explained the extra complexity and made the whole thing easier to read.

I have another article being published on Thursday, so do please read that as well and give me any feedback - good or bad!

Cheers, James


James
MCM [@TheSQLPimp]
Post #1059836
Posted Monday, February 7, 2011 2:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
The added complexity is because my procedure always adjusts the sequence number so that it is continuous and zero based.

James,
While it is true that your routine does this, it seems to me more proper to put that aspect of your code into the Delete routine instead of renumbering everything every time you change the priority on one task.
--
JimFive
Post #1059912
Posted Monday, February 7, 2011 2:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
James,
I would agree that maybe the delete routine would be a better place for the zero re-numbering and that this does over-complicate the routine somewhat.

James


James
MCM [@TheSQLPimp]
Post #1059934
Posted Monday, February 7, 2011 11:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 7:28 AM
Points: 3, Visits: 36
Cool solution for custom sequences. If you don't have immediate application needs, you could just wait for Denali (SQL 11) to go RTM and use the new Sequence Object natively in SQL Server ...
Post #1060030
Posted Tuesday, February 8, 2011 2:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
Oh I'm looking forward to that! Currently looking into the new datetimeoffset data type in 2008 to replace my "convert to local date via a utc hour and minute offset lookup table" function! Looks to be the way forward if you deal with data from multiple time zones. I feel amother article coming on this once I've looked into the pros and cons - as let's face it, there are always some cons! Ever tried using the xml nodes method with a very large xml blob? Not good, the old prepare doc and openxml method is way faster but you only find these things out from testing.

Thanks again for the supportive comments.

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060080
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse