In the table I want to be able to 'move' rows. I have a column called sortid, which automatically on insert is one value higher than the highest value (easily found by selecting top value when sorting my sortid desc).
But I'm a bit confused on what query I'd use for the the 'up/down' actions for each row on my ASP.NET page. What query would I use to select the rows immediately 'below' or 'above' the row to be moved?
Moving a record up or down is done by swapping it with the record before or after, respectively.
If the SortId values are always continuous (i.e. you don't remove records which would cause a gap), then you can just add or subtract one to get the next or previous record. If the records are not guaranteed to be continuous, then you have to find the record next to the one that you want to move.
To find the SortId of the record before:
select max(SortId) from TheTable where SortId < @SortId
To find the SortId of the record after:
select min(SortId) from TheTable where SortId > @SortId
To swap the two records, you can use this trick that calculates one value from the other:
update TheTable
set SortId = @SortId1 + @SortId2 - SortId
where SortId in (@SortId1, @SortId2)
One option is to increment all the sortid fields of the rows "below" just before INSERTing the new row. You can do this with an UPDATE command immediately followed by an INSERT command. Let's say you're inserting a row at position 10. Then you can do something like this:
UPDATE your_table SET sortid = sortid + 1 WHERE sortid >= 10;
INSERT INTO your_table (..., sortid) VALUES (..., 10);
You should probably wrap that up in a transaction to ensure that the operation is carried out atomically.
As for the up/down actions, you wouldn't want to do an INSERT. The above is only appropriate for adding new rows at a particular "location". For the up/down action, you can simply "swap" the sortid value of the two rows, as @Guffa suggested in the other answer.
For getting the rows above the target row.
select * from [TableName]
where sortid > (select sortid from [TableName]
where id = @id)
For the ones below, just use < instead.
