Re Order table

Imagine you have a subset of data which effectively looks like
id - int
sortNumber int
testData varchar
id sortNumber testData
1 1 test1
2 2 test2
3 3 test3
4 4 test4
5 5 test5
6 6 test6
What I want to do is to be able to move a record up and down within the
list.
Therefore, if I want to move record 4 to position 2 ie;
id sortNumber testData
1 1 test1
4 4 test4
2 2 test2
3 3 test4
5 5 test5
6 6 test6
But I want to keep the column 'sortNumber' in ascending order:
id sortNumber testData
1 1 test1
4 2 test4
2 3 test2
3 4 test4
5 5 test5
6 6 test6
The id column is effectively redundent and only used for selecting the
record for update purposes.
Hope someone out there understands what it is I'm trying to do and can
help.
Thanks in advance
Dave.
Whats the best way of approaching this without using a cursor?Try this one
CREATE TABLE T1
(
testData VARCHAR(10) NOT NULL PRIMARY KEY,
pos INT NOT NULL UNIQUE,
id INT NOT NULL UNIQUE
)
INSERT INTO T1 VALUES('A', 1,1)
INSERT INTO T1 VALUES('B', 2,2)
INSERT INTO T1 VALUES('C', 3,3)
INSERT INTO T1 VALUES('D', 4,4)
INSERT INTO T1 VALUES('E', 5,5)
INSERT INTO T1 VALUES('F', 6,6)
DECLARE
@.id AS VARCHAR(10),
@.newpos AS INT,
@.oldpos AS INT
SET @.id = 'D'
SET @.newpos = 2
SET @.oldpos = 4
SELECT T1.*
FROM T1, (SELECT pos AS idpos FROM T1 WHERE testData = @.id) AS P
ORDER BY
CASE
WHEN testData = @.id THEN @.newpos
WHEN pos BETWEEN idpos AND @.newpos THEN pos - 1
WHEN pos BETWEEN @.newpos AND idpos THEN pos + 1
ELSE pos
END
--
UPDATE T1 SET pos =CASE
WHEN testData = @.id THEN @.newpos
WHEN pos BETWEEN @.oldpos AND @.newpos THEN pos - 1
WHEN pos BETWEEN @.newpos AND @.oldpos THEN pos + 1
ELSE pos
END
SELECT * FROM T1
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1166435646.480710.14850@.79g2000cws.googlegroups.com...
> Imagine you have a subset of data which effectively looks like
> id - int
> sortNumber int
> testData varchar
> id sortNumber testData
> 1 1 test1
> 2 2 test2
> 3 3 test3
> 4 4 test4
> 5 5 test5
> 6 6 test6
> What I want to do is to be able to move a record up and down within the
> list.
> Therefore, if I want to move record 4 to position 2 ie;
> id sortNumber testData
> 1 1 test1
> 4 4 test4
> 2 2 test2
> 3 3 test4
> 5 5 test5
> 6 6 test6
> But I want to keep the column 'sortNumber' in ascending order:
> id sortNumber testData
> 1 1 test1
> 4 2 test4
> 2 3 test2
> 3 4 test4
> 5 5 test5
> 6 6 test6
> The id column is effectively redundent and only used for selecting the
> record for update purposes.
> Hope someone out there understands what it is I'm trying to do and can
> help.
> Thanks in advance
> Dave.
> Whats the best way of approaching this without using a cursor?
>