Mysql selects the previous and next data records, sorts the data to move up, move down, and top, and mysql next

Mysql selects the previous and next data records, sorts the data to move up, move down, and top, and mysql next
1. The list sorting function needs to be completed! Shows the effect:
2 setting ideas

Set a rank as the timestamp. By selecting "Move Up", this record is exchanged with the rank value of the previous record. Moving down refers to exchanging the rank value of this record with the rank value of the next record, if it is set to the top, this record is exchanged with the record with the smallest rank value.

3. Select the mysql record as the data structure of the table and the next SQL statement.

SELECT * FROM user ORDER BY rank ASC

+----+------+---------+| ID | rank | name    |+----+------+---------+|  1 |    1 | admin1  ||  2 |    2 | admin2  ||  3 |    3 | admin3  ||  4 |    4 | admin4  ||  5 |    5 | admin5  ||  6 |    6 | admin6  ||  7 |    7 | admin7  ||  8 |    8 | admin8  ||  9 |    9 | admin9  || 10 |   10 | admin10 || 11 |   11 | admin11 || 12 |   12 | admin12 |+----+------+---------+

4. Select the previous row.

select * from user where id <3 or id=(SELECT MIN(id) from user) order by id desc limit 1

+----+------+--------+| ID | rank | name   |+----+------+--------+|  2 |    2 | admin2 |+----+------+--------+1 row in set (0.00 sec)

5. When id = 1

mysql> select * from user where id <1 or id=(SELECT MIN(id) from user) order by id desc limit 1;+----+------+--------+| ID | rank | name   |+----+------+--------+|  1 |    1 | admin1 |+----+------+--------+1 row in set (0.00 sec)

6. Select the next row

mysql> select * from user where id > 3 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1;+----+------+--------+| ID | rank | name   |+----+------+--------+|  4 |    4 | admin4 |+----+------+--------+1 row in set (0.00 sec)

7. When id is the maximum value

mysql> select * from user where id > 12 OR ID=(SELECT MAX(ID) FROM user) ORDER BY id ASC LIMIT 1;+----+------+---------+| ID | rank | name    |+----+------+---------+| 12 |   12 | admin12 |+----+------+---------+1 row in set (0.00 sec)