best way to change sort order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

best way to change sort order

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-11 : 03:54:45

i have a table of products and i allow to change sortorder

so now I have say products

productid sortorder
1 2
2 1
3 3
4 4
5 5

now if they want to move product x at place 5 up then i do
update products set sortorder=sortorder-1 where id=5
but then product 4 & 5 have the same order
what's the easiest way to reorder all of them each time

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-03-11 : 05:44:27

[code]CREATE TABLE #Products (ProductID INT,SortOrder INT)
INSERT INTO #Products (ProductID,SortOrder) VALUES (1,3)
INSERT INTO #Products (ProductID,SortOrder) VALUES (2,2)
INSERT INTO #Products (ProductID,SortOrder) VALUES (3,4)
INSERT INTO #Products (ProductID,SortOrder) VALUES (4,1)
INSERT INTO #Products (ProductID,SortOrder) VALUES (5,5)

SELECT * FROM #products ORDER BY SortOrder

DECLARE @ProductID INT
DECLARE @PosChange INT
SET @ProductID = 1
SET @PosChange = -2

--CREATE PROCEDURE SPChangeSortOrder (@ProductID INT,@PosChange INT)
--AS

DECLARE @DuplicateSortOrder INT
SELECT @DuplicateSortOrder = SortOrder + @PosChange FROM #Products WHERE ProductID = @ProductID

UPDATE p
SET SortOrder = SortOrder - @PosChange
FROM #Products p
WHERE SortOrder >= @DuplicateSortOrder
AND ProductID <> @ProductID

UPDATE #Products
SET SortOrder = SortOrder + @PosChange
WHERE ProductID = @ProductID

SELECT * FROM #Products ORDER BY SortOrder

drop table #products
[/code]

The above code should do the job. You'll have to convert it into a stored procedure but first check it does what you require.
Its a bit of a fudge and you wouldn't neccessarily end up with IDs 1 through 5, but they will be in order so that shouldn't matter.

You can paste the whole thing into SSMS and run it. It runs off a temp table for testing purposes. Take a look, have a play with entering different @ProductIDs and @PosChange amounts and see what you think.

Nick.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-03-11 : 06:24:28

thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-11 : 08:19:25

Much simpler and copes with multiple simultaneous users too...

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		ProductID INT,
		Sortorder INT
	)

-- Populate sample data
INSERT	@Sample
VALUES	(1, 2),
	(2, 1),
	(3, 3),
	(4, 4),
	(5, 5)

-- Display the initial set
SELECT		*
FROM		@Sample
ORDER BY	ProductID

-- Mimic user supplied parameters
DECLARE	@ProductID INT = 5,
	@PosChange INT = -1

-- Do the magic
;WITH cteSortorder(Sortorder, Peso)
AS (
	SELECT	Sortorder,
		ROW_NUMBER() OVER (ORDER BY	CASE ProductID
							WHEN @ProductID THEN SortOrder + @PosChange
							ELSE SortOrder
						END,
						CASE ProductID
							WHEN @ProductID THEN 0
							ELSE 1
						END
		) AS Peso
	FROM	@Sample
)
UPDATE	cteSortorder
SET	Sortorder = Peso

-- Display the final result
SELECT		*
FROM		@Sample
ORDER BY	ProductID

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-01-09 : 04:36:43

I know this was an old topic but I had to do this again so tried peso version - works great for -1 but it doesn't seem to work if I want to move the item the other way

what's the solution

DECLARE	@Sample TABLE
	(
		ProductID INT,
		Sortorder INT
	)

-- Populate sample data
INSERT	@Sample
VALUES	(1, 2),
	(2, 1),
	(3, 3),
	(4, 4),
	(5, 5)

-- Display the initial set
SELECT		*
FROM		@Sample
ORDER BY	ProductID

-- Mimic user supplied parameters
DECLARE	@ProductID INT = 1,
	@PosChange INT = +1

-- Do the magic
;WITH cteSortorder(Sortorder, Peso)
AS (
	SELECT	Sortorder,
		ROW_NUMBER() OVER (ORDER BY	CASE ProductID
							WHEN @ProductID THEN SortOrder + @PosChange
							ELSE SortOrder
						END,
						CASE ProductID
							WHEN @ProductID THEN 0
							ELSE 1
						END
		) AS Peso
	FROM	@Sample
)
UPDATE	cteSortorder
SET	Sortorder = Peso

-- Display the final result
SELECT		*
FROM		@Sample
ORDER BY	ProductID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-09 : 06:26:54

[code]-- Do the magic
;WITH cteSortorder(Sortorder, Peso)
AS (
SELECT Sortorder,
ROW_NUMBER() OVER (ORDER BY CASE ProductID
WHEN @ProductID THEN SortOrder + @PosChange
ELSE SortOrder
END,
CASE ProductID
WHEN @ProductID THEN 0
ELSE -@PosChange
END
) AS Peso
FROM @Sample
)[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources