SQL Server Performance Forum – Threads Archive

Building sort order based on sort column

How do I build an SQL query that can update the sort order based on sort column? Basically, in the UI of my web application, uses can move an item left or right of another item (setting the sort order). That has to translate into an SQL query but I don’t know how to write the query properly (sort of like in facebook or myspace where you can sort albums by moving them left or right of other albums) I came up with the following query but it doesn’t work if a "lower record id" needs to be pushed into the previous record BUT there is no previous record "id" because that record id is the MIN record id.
DECLARE @Id INT; SET @Id = 16260610;
DECLARE @ToId INT;
DECLARE @ToOrderNumber INT;
DECLARE @CurrentRecordOrderNumber INT;
DECLARE @TempOrder INT; SELECT @CurrentRecordOrderNumber = [order] FROM album WHERE id = @id; SELECT * FROM album WHERE id IN
( SELECT MAX(id) FROM album WHERE id <@Id AND userid = 1 AND groupid IS NULL )
AND groupid IS NULL;
SELECT @ToId = id, @ToOrderNumber=[order] FROM album WHERE id IN
( SELECT MAX(id) FROM album WHERE id <@Id AND userid = 1 AND groupid IS NULL )
AND groupid IS NULL; SET @TempOrder = @CurrentRecordOrderNumber;
SET @CurrentRecordOrderNumber = @ToOrderNumber;
SET @ToOrderNumber = @TempOrder; SELECT @CurrentRecordOrderNumber, @ToOrderNumber, @ToId IF( NOT @ToId IS NULL )
BEGIN
UPDATE album SET [order] = @CurrentRecordOrderNumber WHERE id = @Id;
UPDATE album SET [order] = @ToOrderNumber WHERE id = @ToId;
SELECT 1
END
SELECT * FROM album WHERE userid = 1 AND groupid IS NULL ORDER BY [order] ASC;

If I understand you correctly, you can check for this in a
CASE
WHEN EXISTS (SELECT…)
THEN (SELECT …)
ELSE (SELECT …)
END
construct. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Thanks but that’s not it. Consider the following "RESULTSET":
Name, Sort Order
Album #1, 1
Album #5, 2
Album #3, 3
Album #6, 4
Album #7, 5
Album #2, 6
Album #4, 7 The query is: SELECT * FROM album ORDER BY [sortorder] ASC; What I need to do is swap Album #5 and Album #1 SORT ORDER. So Album #5 would have a sort order of 1 and Album #1 would have a sort number of 2.

Ooh, I see. Looks like I thought too complicated. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] So, you’re just looking for a way to swap sort order values in two adjacent sorted albums? What about: <pre id="code"> DECLARE @t TABLE (album VARCHAR(10), sort INT) INSERT INTO @t SELECT ‘#1’, 1 UNION ALL SELECT ‘#5’, 2 SELECT * FROM @t UPDATE t SET t.sort = t1.sort FROM @t t JOIN @t t1 ON (t.album = ‘#5’) AND (t1.album = ‘#1’) OR (t.album = ‘#1’) AND (t1.album = ‘#5’) SELECT * FROM @t album sort ———- ———– #1 1 #5 2 (2 row(s) affected) album sort ———- ———– #1 2 #5 1 (2 row(s) affected) </pre id="code"> – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Thanks, however there is another problem (I been trying to solve this but I have been unable to). I know Album #1 needs to swap places with the "album" before it in the sort order. How do I get the "album before" Album #5 in the sort list? For an example:
Name, Sort order
Album #5, 1
Album #1, 2
Album #4, 3
Album #2, 4
Album #3, 5 In this example, I know that Album #1 needs to move "before" the album before it (sorted by sort order). Therefore, Album #1 must swap places with Album #5. The thing is, I don’t know if Album #5 is the album BEFORE Album #1 in the SORTED LIST. It requires a complicated algorithm and I struggled to solve it but I can’t. Want to take a try?

Sure thing! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] What about: <pre id="code"> CREATE TABLE #t(album VARCHAR(10), sort INT) GO INSERT INTO #t SELECT ‘#1’, 1 UNION ALL SELECT ‘#5’, 2 UNION ALL SELECT ‘#4’, 3 UNION ALL SELECT ‘#2’, 4 UNION ALL SELECT ‘#3’, 5 GO CREATE PROCEDURE dbo.SwapValues(@album VARCHAR(10)) AS SET NOCOUNT ON IF (SELECT sort FROM #t WHERE album = @album) = (SELECT MIN(sort) FROM #t) SELECT ‘Error’ –you would probably want to raise an error here. ELSE BEGIN UPDATE #t SET sort = sort – CASE WHEN album = @album THEN 1 ELSE -1 END WHERE (SELECT sort FROM #t WHERE album = @album) IN(#t.sort, #t.sort + 1) END GO SELECT * FROM #t ORDER BY sort EXEC dbo.SwapValues ‘#5′ SELECT * FROM #t ORDER BY sort EXEC dbo.SwapValues ‘#5′ SELECT * FROM #t ORDER BY sort DROP TABLE #t DROP PROCEDURE dbo.SwapValues (5 row(s) affected) album sort ———- ———– #1 1 #5 2 #4 3 #2 4 #3 5 (5 row(s) affected) album sort ———- ———– #5 1 #1 2 #4 3 #2 4 #3 5 (5 row(s) affected) —– Error album sort ———- ———– #5 1 #1 2 #4 3 #2 4 #3 5 (5 row(s) affected) </pre id="code"> As you can see, the first execution of dbo.SwapValues ‘#5’ swaps album #1 and album #5. Upon completion #5 is the first in the sort order. Therefore the second execution of dbo.SwapValues ‘#5’ yields an error. In your app you would probably want to raise an error and not my SELECT ‘Error’. Anyway the second execution doesn’t change anything. – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

EXCELLENT! However, there’s a problem I found with your query. If Album #5 is moving previously but the previous record doesn’t EXIST it takes the current sort – 1. For an example:
Album #3, 1
Album #2, 2
Album #4, 3
Album #5, 100
If you try to move Album #5 up, it becomes 99…then 98….then 97. How can I make it so it takes the sort order from Album #4 (the previous record) directly. You been a great help on this, thankyou very much (i been trying to solve this for quite some time)

I don’t understand. If non adjacent sort numbers are possible when entering the data, I’m tempted to say that you have a logical flaw there. It could only happen when the user deletes the rows "in between" and for such cases you would either have corresponding logic in a delete procedure or in a delete trigger in place to handle this. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

]]>