Making a column of non-sequential integers, sequential again – help please!

Question

Hi all,

I have a question and hopefully someone can assist. Be gentle – I’m a novice.

I have a table with an integer column called OrdinalPosition, which takes care of the order each entry appears on a web page.

The problem is, that over time some of the rows have been deleted, so rather than running like this in the table…

1

2

3

4

5

6

7

8

9

10

They run like this…

1

2

5

7

8

10

So, now I want to do some ‘house-keeping’

Using the example above I want this to run sequentially…

1

2

3

4

5

6

So, in other words – how to I take some related, but non-sequential integers, and make them sequential again.

I thought that perhaps using a temporary table to copy them out, re-order them and them copy them back somehow???

Any advice most graciously received.

Thanks.

J

Answer 1

There is more than one things wrong with this situation - but rather than focusing on the issues, let's discuss a quick fix.

What version on SQL Server are you running ? If its 2012, you can make use of a the sequence object to generate numbers in sequence and using the temp table approach, move the data and insert it in a re-ordered fashion.

If not 2012, you can use the ROW_NUMBER() Function and still use the temp table approach. This can also be done via an UPDATE statement

Finally, you can also use the temp table approach by adding an identity column to the temp table and then moving row back to the mail table such that the ordinalPosition column is based on the IDENTITY value.

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

Hi,

How's this:

;WITH CTE AS
	(
	 SELECT 1 OrdinalPosition UNION
	 SELECT 2 OrdinalPosition UNION
	 SELECT 5 OrdinalPosition UNION
	 SELECT 7 OrdinalPosition UNION
	 SELECT 8 OrdinalPosition UNION
	 SELECT 10 OrdinalPosition 
	)
	

UPDATE D
SET OrdinalPosition = CorrectedOrdinal
FROM (
	  SELECT OrdinalPosition, 
		     ROW_NUMBER() OVER(ORDER BY OrdinalPosition) CorrectedOrdinal
	  FROM CTE
	 ) D

---

Zach Stagers - http://www.scratchbox.co.uk
Remember to Mark as Answer and Vote as Helpful

Answer 3

First, why do you worry about gaps in the sequence at all?

Second, your suggestion could work if there are no foreign key-constraints.
Third, read this article for a starter http://weblogs.sqlteam.com/peterl/archive/2009/03/10/How-to-efficiently-reuse-gaps-in-identity-column.aspx

---

N 56°04'39.26"
E 12°55'05.63"

Answer 4

http://www.sqlmag.com/article/tsql3/get-rid-of-gaps-in-numerical-sequences

---

Chuck

Answer 5

Thanks for everyone’s feedback and ideas.

So, I ended up getting some help from a colleague on this. Here’s the approach he took with this…

ALTER PROCEDURE [Maintenance].[usp_ReSequenceExcelColumns](
	@ExcelReportID			INT,
	@PerformUpdate			BIT,		-- 0=No (i.e. test only), 1=Yes
	@ExcelColumnIDToMove		INT=0,		-- Allows a newly added column id to be moved to a different ordinal position
	@OrdinalPositionToMoveTo	INT=0)		-- Allows user to specify which position the column should be moved to
AS
BEGIN
	CREATE TABLE #WorkTable1(
		ID			INT IDENTITY,
		ColumnID		INT,
		ColumnName		NVARCHAR(500),
		OldOrdinalPosition	INT)
	INSERT
		#WorkTable1(ColumnID,ColumnName,OldOrdinalPosition)
	SELECT
		ExcelColumnID,
		ColumnName,
		OrdinalPosition
	FROM
		Reports.ExcelColumn
	WHERE
		ExcelReportID = @ExcelReportID
	ORDER BY
		CASE
			WHEN OrdinalPosition = 0 THEN 99999999		-- Ensure that any newly added columns go at the end by default
			ELSE OrdinalPosition
		END
	CREATE TABLE #WorkTable2(
		ID				INT IDENTITY,
		ColumnID			INT,
		ColumnName			NVARCHAR(500),
		OldOrdinalPosition	INT)
	INSERT
		#WorkTable2(ColumnID,ColumnName,OldOrdinalPosition)
	SELECT
		ColumnID,
		ColumnName,
		OldOrdinalPosition
	FROM
		#WorkTable1
	ORDER BY
		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo ELSE ID END,
		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN 1 ELSE 2 END
	DROP TABLE #WorkTable1
	SELECT
		ID AS NewOrdinalPosition,
		OldOrdinalPosition,
		ColumnID,
		ColumnName
	FROM 
		#WorkTable2
	IF @PerformUpdate = 1
		UPDATE
			Reports.ExcelColumn
		SET
			OrdinalPosition = WT.ID
		FROM
			#WorkTable2 WT
		WHERE
			WT.ColumnID = ExcelColumn.ExcelColumnID
	DROP TABLE #WorkTable2
END

Answer 6

Seems to be a lot of bloated and convoluted code there... Hard to maintain.
Take a look at this sleeker implementation!

ALTER PROCEDURE	Maintenance.usp_ReSequenceExcelColumns
(
	@ExcelReportID			INT,
	@PerformUpdate			BIT,		-- 0 = No (i.e. test only), 1 = Yes
	@ExcelColumnIDToMove		INT = 0,	-- Allows a newly added column id to be moved to a different ordinal position
	@OrdinalPositionToMoveTo	INT = 0		-- Allows user to specify which position the column should be moved to
)
AS

SET NOCOUNT ON

IF @PerformUpdate <> 1
	RETURN

;WITH cteTarget(OrdinalPosition, SeqID)
AS (
	SELECT	OrdinalPosition,
		ROW_NUMBER() OVER (ORDER BY	CASE
							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo
							WHEN OrdinalPosition = 0 THEN 99999999
							ELSE OrdinalPosition
						END,
						CASE
							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN 0
							ELSE 1
						END) AS SeqID
	FROM	Reports.ExcelColumn
	WHERE	ExcelReportID = @ExcelReportID
)
UPDATE	cteTarget
SET	OrdinalPosition = SeqID

---

N 56°04'39.26"
E 12°55'05.63"