Building User Defined Sort Order

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)

Building User Defined Sort Order

Author

Topic

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-07-02 : 14:13:28

I have a "Sort order" column in my table where user's can order the data based on intregal value.

For an example:
[album]
albumid
userid
album_name
sort_order

In the front-end UI interface, users can move an album's "sort order" left or right (just like in facebook, myspace etc). The problem is, how do I write the SQL to PROPERLY do this without messing up?

Last night, I tried to solve it but couldn't.

This is the SQL I came up with:

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;

Sample Result Set:
id userid groupid name desc status order
16260617 1 NULL sad123 asdsadsadas 0 1
16260605 1 NULL asd sadasdsa 0 3
16260610 1 NULL sad asdsadasd 0 4
16260616 1 NULL 1 1 0 5
16260618 1 NULL sad sadsadsad 0 6
16260620 1 NULL dasdsad sdas 0 7

The problem is, if I tell record 16260605 to move PREVIOUS of the previous record, it won't move because there is NO record previous than 16260605 (16260605 is the MIN record id in the table for that userid)

Can someone help me build the proper sort order sql or even algorithm?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources