changing order of records

[.nLL]

hi, i've got a problem. i need to change order of records in my table . here
is the table

id forumname
----------------
1 test1
2 test2
3 test3
4 test4

i need to write code that can move nay record up or down without changing
id of forumname. i thought about another column called order

id forumname order
---------------------
1 test1 1
2 test2 2
3 test3 3
4 test4 4

but couldnt figureout how to change that order column. any one got an idea?

[xyb]

move up:
declare @id int
declare @order int
select @id = 2
begin tran
select @order = order from table where id = @id
update table set order = @order where id in (select top 1 from table
where order < @order order by order desc)
update table set order = @order - 1 where id = @id
end tran

[Roy Harvey]

SQL Server tables don't really have an order, you need to impose one
using ORDER BY based on the data in the table(s). Sometimes that just
means ORDER BY id. Other times that means creating another column to
let you control the order.

As to assigning values to that column, perhaps if you provide a clear
explanation of what rule you will be using to order the rows someone
will have some advice or ideas. Or perhaps they will suggest a way to
avoid adding the column altogether.

Roy Harvey
Beacon Falls, CT

[.nLL]

basicaly my table holds forum names for different users. in my system user
can create forums and systems aves them to this table and lists accordingly
when you browse users forums

i need an idea that will allow user move his/her forums up or down in their
own forum list.

sayi have 2 users

id forumname owner
----------------------
1 test1 usera
2 test2 userb
3 test3 userb
4 test4 usera

when you go to usera's forum (currently) system lists them by id asc so it
is

test1
test4

i want to give user a option so he/she can change order of listing

"Roy Harvey" <roy_h...@snet.net> wrote in message
news:5fju63lr7051kfd05...@4ax.com...

[--CELKO--]

Here is an old "cut & paste" for this problem: Given a motorpool with
numbered parking spaces, you want to move the automobiles around.
CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);

Re-arrange the display order based on the parking_space column:

CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND
@new_parking_space
OR parking_space BETWEEN @new_parking_space AND
@old_parking_space;

When you want to drop a few rows, remember to close the gaps with
this:

CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);