Details on using order by in MySQL

1. Introduction

When using the select statement, you can combine order by to sort the queried data. If you do not use order by , the data set returned by MySQL is in the same order as it is in the underlying table by default. This may or may not be consistent with the order in which you added the data to the table ( MySQL will sort the memory when you modify or delete the table, and the order of the data will change). Therefore, if we want the data to be in order, we should specify the sorting method.

2. Main text

First, prepare a User table. DDL and table data are as follows and can be copied and used directly.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'User name',
  `age` int(11) NOT NULL COMMENT 'Age',
  `sex` smallint(6) NOT NULL COMMENT 'Gender',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '李子８', 18, 1);
INSERT INTO `user` VALUES (2, '张三', 22, 1);
INSERT INTO `user` VALUES (3, '李四', 38, 1);
INSERT INTO `user` VALUES (4, '王五', 25, 1);
INSERT INTO `user` VALUES (5, '六麻子', 13, 0);
INSERT INTO `user` VALUES (6, '田七', 37, 1);
INSERT INTO `user` VALUES (7, 'Thank you', 18, 1);

SET FOREIGN_KEY_CHECKS = 1;

The initial order of the data is as follows:

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

2.1 Sorting a single column

Let's first look at using order by to sort a single column.

need:

Sort by user age in ascending order.

Statement:

select * from user order by age;

result:

mysql> select * from user order by age;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 5 | Liu Mazi | 13 | 0 |
| 1 | Plum 8 | 18 | 1 |
| 7 | Thank you | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tianqi | 37 | 1 |
| 3 | Li Si | 38 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

analyze:

You can see that the output order of user table is sorted in ascending order of age , so you can guess that the default sorting method MySQL is ascending. In addition, I use the * wildcard to query all columns, and do not explicitly specify age column. In fact, the columns following MySQL 's order order by do not have to be queried, such as select name from user order by age ; this will have the same effect.

2.2 Sorting multiple columns

order by can not only sort a single column, it can also sort multiple columns. Just put the columns to be sorted after order by .

Before testing, let's add a piece of data with the same age to the table.

mysql> insert into user (name, age, sex) values ​​('李子柒', 18, 1);
Query OK, 1 row affected (0.01 sec)

need:

Sort by user age in ascending order and then by user name.

Statement:

select * from user order by age, name;

result:

mysql> select * from user order by age, name;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 5 | Liu Mazi | 13 | 0 |
| 1 | Plum 8 | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
| 7 | Thank you | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tianqi | 37 | 1 |
| 3 | Li Si | 38 | 1 |
+----+--------+-----+-----+
8 rows in set (0.00 sec)

analyze:

order by can be applied to multiple columns, MySQL will sort the columns completely according to the order of the columns after order by . However, MySQL 's sorting of Chinese characters needs to consider the issue of database character set encoding. If you don't understand it very well, it is recommended not to sort Chinese characters, because in most cases we need to get the pinyin sorting results, but it is often unsatisfactory! In addition, here we do not have a sorting method after age and name, so the default is ascending order, first sorting by age in ascending order and then by name in ascending order. If you need to use descending order, you need to specify DESC , for example, select id, name, age from user order by age, name desc;

2.3 Sorting method

There are two sorting methods for order by:

ASC -> Sort in ascending order (default sorting method)
DESC -> Sort in descending order

Note: As mentioned above, order by sorts multiple columns, but the sorting method only works on one column. For example, if you need to sort the data in the user table in descending order by both age and name, you should specify descending order for both columns.

mysql> select * from user order by age desc, name desc;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 3 | Li Si | 38 | 1 |
| 6 | Tianqi | 37 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 2 | Zhang San | 22 | 1 |
| 7 | Thank you | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
| 1 | Plum 8 | 18 | 1 |
| 5 | Liu Mazi | 13 | 0 |
+----+--------+-----+-----+
8 rows in set (0.00 sec)

If you only specify descending sorting of age column and do not specify name column, MySQL will sort the data in descending order based on age , and then perform a default ascending sort based on name column.

mysql> select * from user order by age desc, name;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 3 | Li Si | 38 | 1 |
| 6 | Tianqi | 37 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 2 | Zhang San | 22 | 1 |
| 1 | Plum 8 | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
| 7 | Thank you | 18 | 1 |
| 5 | Liu Mazi | 13 | 0 |
+----+--------+-----+-----+
8 rows in set (0.00 sec)

It can be seen that the sorting method of the data in the three rows of Li Ziba, Li Ziqi and Xie Li has changed.

2.4 order by combined with limit

order by combined with limit can obtain the number of sorted data row records. For example, we get the oldest user from the user table. We can see that Comrade Li Si is 38 years old, which makes him quite old and one of the oldest people.

mysql> select * from user order by age desc limit 1;
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 3 | Li Si | 38 | 1 |
+----+------+-----+-----+
1 row in set (0.00 sec)

limit needs to follow order by . If the position is incorrect, MySQL will throw an exception.

mysql> select * from user limit 1 order by age des;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by age des' at line 1

This is the end of this article about the details of using order by in MySQL. For more information about the use of order by in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!