Sort all rows based on position column

Question

I have the following sortable table:

| id | txt | position |
|----|-----|----------|
| 1  | aaa | 1        |
| 2  | bbb | 2        |
| 3  | ccc | 3        |

Now I want to change the position order. Say I want to move row 3 to position 1 keeping the order of the other rows intact:

| id | txt | position |
|----|-----|----------|
| 1  | aaa | 2        |
| 2  | bbb | 3        |
| 3  | ccc | 1        |

I can make this happen with this query after updating the row I want to be on top to position 0:

SET @row_number:=0;
UPDATE 
sortable,
(
    SELECT 
    @row_number:=ifnull(@row_number, 0)+1 AS new_position,
    id 
    FROM sortable
    ORDER BY position
) AS table_position
SET position=table_position.new_position
WHERE table_position.id=sortable.id;

This works perfectly fine when moving a row to the first position. But it gets in trouble when trying to move a row to the second (or any other) position for example.

I'd like some help fixing the query so I can move any row to any position and it'll update the other rows accordingly. So if I move 3 to 2, 2 becomes 3. If I move 1 to 3, 2 becomes 1 and 3 becomes 2. I hope you get the idea. Obviously there will be more rows in the real world data. This is just an example.

Imagine that initial row number is M, and its new value is N. If M > N, then the rows with initial numbers from M to N-1 will increase by 1. If M < N, then the rows with initial numbers from M+1 to N will decrease by 1. The rest is easy - do it itself. — Akina, Nov 08 '20 at 17:41

score 1 · Answer 1 · answered Nov 08 '20 at 17:45

1

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(txt VARCHAR(12) PRIMARY KEY
,position INT NOT NULL
);

INSERT INTO my_table VALUES
('aaa',1),
('bbb',2),
('ccc',3),
('ddd',4),
('eee',5),
('fff',6);

Let's say we want to move the text at position 4 to position 1...

SELECT *
            , CASE WHEN position < 4 THEN position +1 
                   WHEN position = 4 THEN 1
                   ELSE position END new_pos
         FROM my_table
         
   +-----+----------+---------+
   | txt | position | new_pos |
   +-----+----------+---------+
   | aaa |        1 |       2 |
   | bbb |        2 |       3 |
   | ccc |        3 |       4 |
   | ddd |        4 |       1 |
   | eee |        5 |       5 |
   | fff |        6 |       6 |
   +-----+----------+---------+

...which we can rewrite as an UPDATE...

UPDATE my_table x
  JOIN 
     ( SELECT *
            , CASE WHEN position < 4 THEN position +1 
                   WHEN position = 4 THEN 1
                   ELSE position END new_pos
         FROM my_table
     ) y
    ON y.txt = x.txt
   SET x.position = y.new_pos;
   
SELECT * FROM my_table;
   +-----+----------+
   | txt | position |
   +-----+----------+
   | aaa |        2 |
   | bbb |        3 |
   | ccc |        4 |
   | ddd |        1 |
   | eee |        5 |
   | fff |        6 |
   +-----+----------+

answered Nov 08 '20 at 17:45

Strawberry

33,750
13
40
57

Thank you! Trying to work with this one because this too works fine when moving to position 1, but not so much when moving to position 2. Or moving position 1 to position 4 instead for example. Trying to make a universal query that can handle those cases as well – icecub Nov 08 '20 at 18:02
Does this help? https://stackoverflow.com/questions/62660126/change-position-of-serial-number-in-sql/62660956#62660956 – Strawberry Nov 08 '20 at 22:30
It's very close! It moves correctly on the first use, but not any subsequent moves. Say you have 5 rows and you move 4 to 2, get: `1,3,4,2,5` (which is correct). But if you move 3 to 5 from here, you get `1,5,4,2,5`. I think the missing piece of this puzzle would be sorting first to solve this issue. And maybe involve `MAX()` to prevent counting +1 on highest row, but not sure about that. For now I'm going to sleep (it's nearly 2 am here). I do appreciate your help though and I will obviously upvote / mark as answered once I get this figured out. Not in the habit of forgetting this, haha – icecub Nov 09 '20 at 00:43
Perhaps I'm missing something, but I can't replicate this observation: https://www.db-fiddle.com/f/wnAM7vAbWPzEu8MvWTSBqv/1 – Strawberry Nov 10 '20 at 05:48
My explanation here was just very bad. It took 3 other people that I happened to know to finally figure it out (one of them being the person here that answered as well). So it's not your fault. The problem wasn't the moving. It was consecutive moves in different directions from there on out. I was moving based on the position column number (as I explained in my question), while I should've been moving based on the row number. If position 4 was set at row 2, and I wanted to move it to row 3, it should be: Move 2 to 3. Not move 4 to 3. – icecub Nov 10 '20 at 13:33

Robert Groves · Accepted Answer · 2020-11-10T00:45:13.560

Something like this should work:

SET @old_number:=3;
SET @new_number:=5;

UPDATE my_table x
JOIN
(
    SELECT *
    , CASE WHEN @old_number > @new_number THEN
                CASE WHEN position = @old_number THEN @new_number
                     WHEN position >= @new_number AND position < @old_number THEN position +1
                     ELSE position END 
           WHEN @old_number < @new_number THEN
                CASE WHEN position = @old_number THEN @new_number
                     WHEN position <= @new_number AND position > @old_number THEN position -1
                     ELSE position END 
           ELSE position END new_position
    FROM my_table
    ORDER BY position
) y
ON y.position = x.position
SET x.position = y.new_position;

If you do some pre-work in setting variables up front, this becomes a little cleaner:

SET @old_position:=1;
SET @new_position:=5;

SET @low_position = LEAST(@old_position, @new_position);
SET @high_position = GREATEST(@old_position, @new_position);
SET @position_modifier = CASE WHEN @old_position > @new_position THEN 1 ELSE -1 END;

UPDATE my_table x
JOIN
(
    SELECT *
    , CASE WHEN position = @old_position THEN @new_position
           WHEN position BETWEEN @low_position AND @high_position THEN position + @position_modifier
           ELSE position 
      END new_position
    FROM my_table
    ORDER BY position
) y
ON y.position = x.position
SET x.position = y.new_position;

In the latter version, the order of the WHEN clauses in the CASE statement of the sub-query are important.

Essentially the positions need to be adjusted accordingly:

When the old position number is greater than the new position number, all positions between the old and new position (exclusive of the old position) need to be incremented.
When the old position number is less than the new position number, all positions between the old and new position (exclusive of the old position) need to be decremented.

Note BETWEEN is inclusive of the range which is why the ordering of the WHEN clauses are important. You want the match of the current position to the old position number to happen first so that it doesn't fall into the WHEN with the BETWEEN test.

Sort all rows based on position column

2 Answers2