Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack