Create a custom order in SQL

Question

Create a custom order in SQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

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

id | name | description | display_order | ...

The display_order field was set to UNIQUE and should be an integer. Its main function is to be used to create a custom order in the table, using ORDER BY :

SELECT * FROM groups ORDER BY display_order;

However, I need to create a query to raise or lower this order, so I also change the top and bottom records.

View a representative image of what I need to do: link .

+----+-----------------+-------------+--------------------+
| 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 upload the "Moderators" group up, as shown in the image, based on ID 2 , what would the query look like?

sql order-by

asked by anonymous 19.05.2018 / 03:51

2 answers

Pull data from a table for inputs on the same page How to generate Dynamic Forms in Google Maps Api JS?

score 0 · Answer 1

One possible solution would be to switch the display_order column to decimal with 1 home.

So you can upload:

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 reorder the logs:

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

Beware of competing processes that can mess things up, do not use INNODB because it only does LOCK on the ROW that is messing around, ideally using MyISAM and working with 2 Stored Procedures, MyISAM will do LOCK on TABLE while running procedure, so you do not have to worry about competing processes, now if you are in a scenario where this table has many records this solution will not answer 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 ;

score 0 · Answer 2

Assuming you can not 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                  |
+----+-----------------+-------------+--------------------+

You can simply sort by id :)

SELECT *
  FROM groups
 ORDER BY ID

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

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

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 sorting, the Moderators group will be listed after the Administrators group, followed by all remaining groups, according to the value of display_order

Both alternatives result in:

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

Link to SQLFiddle