Moving Rows 'Up and Down' in a SQL Database

Moving rows 'up and down' in a SQL database

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)

SQL to move rows up or down in two-table arrangement

I prepared a complete demo for you how this can work on data.stackexchange.com.

The solution is tailored to your comment:

the move up or down can be only a single step - in other words, one
cannot move 2 or more positions

In the example I make John trade ordinal positions with Jack above him:

WITH x AS (
  SELECT t2.itmid, t2.ord
  FROM   dbo.test_db_002 t1
  LEFT   JOIN dbo.test_db_003 t2 ON (t1.id = t2.itmid)
  WHERE  t1.name = 'John'  -- must be unique, or query by id ...
  )
  , y AS (
  SELECT TOP 1
         t.itmid, t.ord
  FROM   dbo.test_db_003 t, x
  WHERE  t.ord < x.ord     -- smaller ord = "above"
  ORDER  BY t.ord DESC
  )
UPDATE dbo.test_db_003 SET ord = z.ord
FROM (
   SELECT x.itmid, y.ord FROM x,y
   UNION ALL
   SELECT y.itmid, x.ord FROM x,y
   ) z
WHERE  dbo.test_db_003.itmid = z.itmid   

###Major points:

0. Use two CTE to structure the query:
1. Get John's id & ordinal position
2. Get the same for the person above him
3. Prepare two rows where these two switch ordinal numbers with the help of UNION ALL
4. Use these two rows in a now simple UPDATE

• The ordinal position ord must allow passing duplicates for this to work.
• If there is nobody 'above', the query will silently do nothing.

SQL: How to change a row order position

I'm not entirely clear how you intend for the reordering operation to work. Is this what you had in mind?

update T
set position =
    case    
        when newPosition > oldPosition then
            case when position = least(oldPosition, newPosition)
                then newPosition else position - 1 end
        else /* newPosition < oldPosition */
            case when position = greatest(oldPosition, newPosition)
                then newPosition else position + 1 end
    end    
where position between
           least(oldPosition, newPosition)
    and greatest(oldPosition, newPosition)
    and oldPosition <> newPosition

Move one exact row to top of all rows leaving all other ASCENDING

Use a case in the order by:

SELECT *
FROM TABLE_NAME
ORDER BY (CASE WHEN NAME = 'United Kingdom' THEN 1 ELSE 2 END), Name;

ORDER BY accepts multiple keys. The first puts the desired values first.

Moving rows in sqlite database

The documentation says:

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.

(This is true for all SQL databases.)

So you cannot rely on the order that the rows happen to be stored in; you have to use some value in some table column.

Moving Rows up and down using Mysqli in PHP (Update SQL)

Using your current UPDATE statement:

$update = $mysqli->prepare('
  UPDATE links
  SET    `order`=(CASE `order` WHEN ? THEN ? ELSE ? END)
  WHERE  `order`=? OR `order`=?
');
$update->bind_param('iiiii', $a, $b, $a, $a, $b);
$update->execute();

However, I'd be tempted to do something more like this (which allows one to shift items an arbitrary number of places):

$update = $mysqli->prepare('
  UPDATE links
  SET    `order`= `order` '.($a < $b ? '+' : '-').' 1
  WHERE  `order` BETWEEN ? AND ?
');
$update->bind_param('ii', min($a,$b), max($a,$b));
$update->execute();

Reorder MySQL rows: move row up

As pointed out by @C4ud3x and @Hanno Binder, there is no guarantee that MySQL returns the data in the same order as they were inserted. Normally, this is the case, but it is not a robust way to handle ordering of the data. Thus I solved the problem by adding a column order_id to be able to use the ORDER BY clause to ensure that the order will be maintained properly.

I found a good answer to a related question over at dba.stackexchange.com. The main point from the accepted answer there is:

If, on the other hand, you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

---

Related Topics