Moving rows 'up and down' in a SQL database

Question

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?

Answer 1

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)