Create a custom SQL order

Question

I have a table (groups) in SQL with the following structure:

id | name | description | display_order | ...

The field display_order was defined as UNIQUE and must be an integer. Its main function is to be used to create a custom order in the table, through the ORDER BY:

SELECT * FROM groups ORDER BY display_order;

However, I need to create a query to move this order up or down, so that I also change the top and bottom records.

See a representative image what I need to do: http://prntscr.com/jjrxh1.

+----+-----------------+-------------+--------------------+
| id |       name      | description | display_order  (↓) |
+----+-----------------+-------------+--------------------+
| 1  | Administradores | ...         | 1                  |
+----+-----------------+-------------+--------------------+
| 3  | Outro Grupo     | ...         | 2                  |
+----+-----------------+-------------+--------------------+
| 2  | Moderadores     | ...         | 3                  |
+----+-----------------+-------------+--------------------+
| 4  | Grupo X         | ...         | 4                  |
+----+-----------------+-------------+--------------------+

Assuming, therefore, that I need to move the "Moderators" group up as shown in the image, based on ID 2, what the query would look like?

Answer 1

One possible solution would be to swap the display_order column to decimal with 1 house.

So you can climb:

UPDATE groups SET display_order=display_order - 1.1 WHERE id=X;

So you can go down:

UPDATE groups SET display_order=display_order + 1.1 WHERE id=X;

Then re-sort the records:

UPDATE groups G1 SET display_order=(SELECT COUNT(*) FROM groups G2 WHERE G1.display_order<G2.display_order)

Beware of concurrent processes that can mess things up, don’t use INNODB because it only does LOCK on the ROW that is stirring, the ideal is to use Myisam and work with 2 Stored Procedures, Myisam will LOCK on TABLE while running the process, so you don’t have to worry about competing processes, now if you are in a scenario where this table has many records this solution will not suit you because it will generate slowness, follow procedures in Mysql:

DELIMITER //
    CREATE PROCEDURE IF NOT EXISTS SP_GROUP_SOBE(IN SP_PARAM_GROUP_ID INT)
    BEGIN
        UPDATE groups SET display_order=display_order - 1.1 WHERE id=SP_PARAM_GROUP_ID;
        UPDATE groups G1 SET display_order=(SELECT COUNT(*) FROM groups G2 WHERE G1.display_order<G2.display_order);
    END //
DELIMITER ;

DELIMITER //
    CREATE PROCEDURE IF NOT EXISTS SP_GROUP_DESCE(IN SP_PARAM_GROUP_ID INT)
    BEGIN
        UPDATE groups SET display_order=display_order + 1.1 WHERE id=SP_PARAM_GROUP_ID;
        UPDATE groups G1 SET display_order=(SELECT COUNT(*) FROM groups G2 WHERE G1.display_order<G2.display_order);
    END //
DELIMITER ;

Answer 2

For SQL Server, it could be done as follows:

UPDATE groups
SET groups.display_order = X.ORDEM
FROM groups
INNER JOIN
(
    SELECT 
    ROW_NUMBER() OVER(ORDER BY id) AS ORDEM, 
    * 
    FROM groups 
) AS X ON
X.id = groups.id

With this, you would update the record to order according to the id field.

Now if your goal is to change "physically" the ordering, you can use grouping/sorting indexes.

Answer 3

Assuming that you cannot change the table by updating the value of display_order, here are some alternatives to sort a table with the following content:

+----+-----------------+-------------+--------------------+
| id |       name      | description | display_order  (↓) |
+----+-----------------+-------------+--------------------+
| 1  | Administradores | ...         | 1                  |
+----+-----------------+-------------+--------------------+
| 3  | Outro Grupo     | ...         | 2                  |
+----+-----------------+-------------+--------------------+
| 2  | Moderadores     | ...         | 3                  |
+----+-----------------+-------------+--------------------+
| 4  | Grupo X         | ...         | 4                  |
+----+-----------------+-------------+--------------------+

1. You can just sort by id :)

SELECT *
  FROM groups
 ORDER BY ID

2. Another alternative is to force the order you want, for example:

SELECT * 
  FROM groups 
 ORDER BY name = 'Administradores' DESC, name = 'Moderadores' DESC, display_order;

3. or more explicitly:

SELECT * 
  FROM groups 
 ORDER BY CASE 
            WHEN name = 'Administradores' THEN 1
            WHEN name = 'Moderadores' THEN 2
            ELSE display_order
          END;

With this order, the Moderators group will be listed after the Administrators group, followed by all remaining groups according to the value of display_order

The two alternatives result in:

+----+-----------------+-------------+--------------------+
| id |       name      | description | display_order  (↓) |
+----+-----------------+-------------+--------------------+
| 1  | Administradores | ...         | 1                  |
+----+-----------------+-------------+--------------------+
| 2  | Moderadores     | ...         | 3                  |
+----+-----------------+-------------+--------------------+
| 3  | Outro Grupo     | ...         | 2                  |
+----+-----------------+-------------+--------------------+
| 4  | Grupo X         | ...         | 4                  |
+----+-----------------+-------------+--------------------+

Link to the Sqlfiddle