Mysql sorting and paging (order by & limit) and existing pitfalls

Sorting query (order by)

In e-commerce: We want to view all orders completed today and sort them from high to low according to transaction amount. At this time, we can use the sorting function in the database to complete it.

Sorting syntax:

select field name from table name order by field1 [asc|desc], field2 [asc|desc];

The fields that need to be sorted follow the order by;
asc|desc indicates the sorting rule, asc: ascending order, desc: descending order, the default is asc;
Supports sorting by multiple fields, and multiple fields are separated by commas.

Single field sorting

mysql> create table test2(a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2 values (10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+------+----------+
| a | b |
+------+----------+
| 10 | jack |
| 8 | tom |
| 5 | ready |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a asc;
+------+----------+
| a | b |
+------+----------+
| 5 | ready |
| 8 | tom |
| 10 | jack |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a desc;
+------+----------+
| a | b |
+------+----------+
| 100 | javacode |
| 10 | jack |
| 8 | tom |
| 5 | ready |
+------+----------+
4 rows in set (0.00 sec)
mysql> select * from test2 order by a;
+------+----------+
| a | b |
+------+----------+
| 5 | ready |
| 8 | tom |
| 10 | jack |
| 100 | javacode |
+------+----------+
4 rows in set (0.00 sec)

Multiple fields sorting

For example, in the student table, first sort by student age in descending order, and then sort by student ID in ascending order if the ages are the same, as follows:

mysql> create table stu(id int not null comment 'student number' primary key,age tinyint not null comment 'age',name varchar(16) comment 'name');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into stu (id,age,name) values (1001,18,'路人甲Java'),(1005,20,'刘德华'),(1003,18,'張薛友'),(1004,20,'張國荣'),(1010,19,'梁朝伟');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from stu;
+------+-----+---------------+
| id | age | name |
+------+-----+---------------+
| 1001 | 18 | Passerby Java |
| 1003 | 18 | Jacky Cheung |
| 1004 | 20 | Leslie Cheung |
| 1005 | 20 | Andy Lau |
| 1010 | 19 | Tony Leung Chiu Wai |
+------+-----+---------------+
5 rows in set (0.00 sec)
mysql> select * from stu order by age desc,id asc;
+------+-----+---------------+
| id | age | name |
+------+-----+---------------+
| 1004 | 20 | Leslie Cheung |
| 1005 | 20 | Andy Lau |
| 1010 | 19 | Tony Leung Chiu Wai |
| 1001 | 18 | Passerby Java |
| 1003 | 18 | Jacky Cheung |
+------+-----+---------------+
5 rows in set (0.00 sec)

Sort by alias

mysql> select * from stu;
+------+-----+---------------+
| id | age | name |
+------+-----+---------------+
| 1001 | 18 | Passerby Java |
| 1003 | 18 | Jacky Cheung |
| 1004 | 20 | Leslie Cheung |
| 1005 | 20 | Andy Lau |
| 1010 | 19 | Tony Leung Chiu Wai |
+------+-----+---------------+
5 rows in set (0.00 sec)
mysql> select age 'age',id as 'student number' from stu order by ageasc,student numberdesc;
+--------+--------+
| Age| Student ID|
+--------+--------+
| 18 | 1003 |
| 18 | 1001 |
| 19 | 1010 |
| 20 | 1005 |
| 20 | 1004 |
+--------+--------+

Sort by function

There is a student table (id: number, birth: date of birth, name: name), as follows:

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE student (
  -> id int(11) NOT NULL COMMENT 'student number',
  -> birth date NOT NULL COMMENT 'Date of birth',
  -> name varchar(16) DEFAULT NULL COMMENT 'Name',
  -> PRIMARY KEY (id)
  -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student (id,birth,name) values (1001,'1990-10-10','Passerby Java'),(1005,'1960-03-01','Andy Lau'),(1003,'1960-08-16','Jacky Cheung'),(1004,'1968-07-01','Leslie Cheung'),(1010,'1962-05-16','Tony Leung');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM student;
+------+------------+---------------+
| id | birth | name |
+------+------------+---------------+
| 1001 | 1990-10-10 | Passerby Java |
| 1003 | 1960-08-16 | Jacky Cheung |
| 1004 | 1968-07-01 | Leslie Cheung |
| 1005 | 1960-03-01 | Andy Lau |
| 1010 | 1962-05-16 | Tony Leung Chiu Wai |
+------+------------+---------------+
5 rows in set (0.00 sec)

Requirement: Query the number, date of birth, year of birth, and name in ascending order of birth year and number. There are two ways to write them as follows:

mysql> SELECT id number,birth date of birth,year(birth) year of birth,name name from student ORDER BY year(birth) asc,id asc;
+--------+--------------+--------------+---------------+
| Number| Date of Birth| Year of Birth| Name|
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | Jacky Cheung |
| 1005 | 1960-03-01 | 1960 | Andy Lau |
| 1010 | 1962-05-16 | 1962 | Tony Leung Chiu Wai |
| 1004 | 1968-07-01 | 1968 | Leslie Cheung |
| 1001 | 1990-10-10 | 1990 | Passerby Java |
+--------+--------------+--------------+---------------+
5 rows in set (0.00 sec)
mysql> SELECT id number,birth date of birth,year(birth) year of birth,name name from student ORDER BY year of birth asc,id asc;
+--------+--------------+--------------+---------------+
| Number| Date of Birth| Year of Birth| Name|
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | Jacky Cheung |
| 1005 | 1960-03-01 | 1960 | Andy Lau |
| 1010 | 1962-05-16 | 1962 | Tony Leung Chiu Wai |
| 1004 | 1968-07-01 | 1968 | Leslie Cheung |
| 1001 | 1990-10-10 | 1990 | Passerby Java |
+--------+--------------+--------------+---------------+
5 rows in set (0.00 sec)

illustrate:
year function: It is a date function that can get the year in the corresponding date.
There are two ways of sorting above. The first one is to use a function in order by, and the second one is to use alias sorting.

Sort after where

The order data is as follows:

mysql> drop table if exists t_order;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t_order(
  -> id int not null auto_increment comment 'Order number',
  -> price decimal(10,2) not null default 0 comment 'Order amount',
  -> primary key(id)
  -> )comment 'Order Table';
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t_order;
+----+--------+
| id | price |
+----+--------+
| 1 | 88.95 |
| 2 | 100.68 |
| 3 | 500.00 |
| 4 | 300.00 |
| 5 | 20.88 |
| 6 | 200.50 |
+----+--------+
6 rows in set (0.00 sec)

Requirement: Query the order amount >= 100, sort by order amount in descending order, and display 2 columns of data, column headers: order number, order amount, as follows:

mysql> select a.id order number, a.price order amount from t_order a where a.price>=100 order by a.price desc;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
+--------------+--------------+
4 rows in set (0.00 sec)

Limit Introduction

Limit is used to limit the number of rows returned by a select query and is often used for operations such as paging.

grammar:

select column from table limit [offset,] count;

illustrate:

offset: represents the offset, in layman's terms, how many rows to skip. offset can be omitted and defaults to 0, which means skipping 0 rows. Range: [0, +∞).
count: skip the offset row and start fetching data, fetching count rows of records; range: [0, +∞).
The values of offset and count in limit cannot use expressions.

Below we list some commonly used examples to deepen your understanding.

Get the first n rows of records

select columns from table limit 0,n;
Or select columns from table limit n;

For example, get the first two records of an order as follows:

mysql> create table t_order(
  -> id int not null auto_increment comment 'Order number',
  -> price decimal(10,2) not null default 0 comment 'Order amount',
  -> primary key(id)
  -> )comment 'Order Table';
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t_order;
+----+--------+
| id | price |
+----+--------+
| 1 | 88.95 |
| 2 | 100.68 |
| 3 | 500.00 |
| 4 | 300.00 |
| 5 | 20.88 |
| 6 | 200.50 |
+----+--------+
6 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a limit 2;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 1 | 88.95 |
| 2 | 100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a limit 0,2;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 1 | 88.95 |
| 2 | 100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)

Get the largest record

We need to get the record with the largest order amount. We can do this by sorting by amount in descending order, and then taking the first record, as follows:

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
6 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 1;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
+--------------+--------------+
1 row in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 0,1;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
+--------------+--------------+
1 row in set (0.00 sec)

Get records ranked from n to m

We need to skip n-1 records first, and then take m-n+1 records, as follows:

select columns from table limit n-1,m-n+1;

For example, if we want to get the records of the top 3 to 5 with the highest order amount, we need to skip 2 records and then get 3 records, as follows:

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
6 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 2,3;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
+--------------+--------------+
3 rows in set (0.00 sec)

Pagination Query

During the development process, we often use paging, which generally has two parameters:
page: indicates the page number, starting from 1, ranging from [1, +∞)
pageSize: how many records are displayed per page, range [1, +∞)
For example: page = 2, pageSize = 10, which means getting 10 data items on page 2.
We use limit to implement paging. The syntax is as follows:

select column from table name limit (page - 1) * pageSize, pageSize;

Requirement: We display 2 items per page in descending order of order amount, and obtain all order data, page 1, page 2, and page 3 data in sequence, as follows:

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
6 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 0,2;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 2,2;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 6 | 200.50 |
| 2 | 100.68 |
+--------------+--------------+
2 rows in set (0.00 sec)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 4,2;
+--------------+--------------+
| Order Number| Order Amount|
+--------------+--------------+
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
2 rows in set (0.00 sec)

Avoid pitfalls

Expressions cannot be used in limit

mysql> select * from t_order where limit 1,4+1;
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 'limit 1,4+1' at line 1
mysql> select * from t_order where limit 1+0;
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 'limit 1+0' at line 1
mysql>

Conclusion: limit can only be followed by a specific number.

The two numbers after limit cannot be negative.

mysql> select * from t_order where limit -1;
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 'limit -1' at line 1
mysql> select * from t_order where limit 0,-1;
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 'limit 0,-1' at line 1
mysql> select * from t_order where limit -1,-1;
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 'limit -1,-1' at line 1

The pitfalls of sorting paging

Prepare the data:

mysql> insert into test1 (b) values (1),(2),(3),(4),(2),(2),(2),(2);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
+---+---+
8 rows in set (0.00 sec)
mysql> select * from test1 order by b asc;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
8 rows in set (0.00 sec)

Next, we get the data in ascending order of b, with 2 records per page.

The following sql is the data of page 1, page 2, page 3, page 4, and page 5, as follows:

mysql> select * from test1 order by b asc limit 0,2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc limit 2,2;
+---+---+
| a | b |
+---+---+
| 8 | 2 |
| 6 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc limit 4,2;
+---+---+
| a | b |
+---+---+
| 6 | 2 |
| 7 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc limit 6,2;
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc limit 7,2;
+---+---+
| a | b |
+---+---+
| 4 | 4 |
+---+---+
1 row in set (0.00 sec)

There are 2 questions above:

Question 1: Look at the second and third sql. They are the data of page 2 and page 3 respectively. The same data appears in the results. Are you confused?

Question 2: There are only 8 records in the entire table, so how come data on page 5 appears? I am confused again.

Let's analyze the above reasons: the main reason is that the b field has the same value. When the same value exists during the sorting process and there are no other sorting rules, MySQL is confused and doesn't know how to sort.

Just like when we line up in school, we sort by height, so how do we sort when we have the same height? Those with the same height are arranged randomly.

Suggestion: When there are identical values in the sort, you need to specify another sorting rule. This sorting rule does not have ambiguity. For example, you can add a descending order to the above, as follows:

mysql> select * from test1 order by b asc,a desc;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 8 | 2 |
| 7 | 2 |
| 6 | 2 |
| 5 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
8 rows in set (0.00 sec)
mysql> select * from test1 order by b asc,a desc limit 0,2;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 8 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc,a desc limit 2,2;
+---+---+
| a | b |
+---+---+
| 7 | 2 |
| 6 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc,a desc limit 4,2;
+---+---+
| a | b |
+---+---+
| 5 | 2 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc,a desc limit 6,2;
+---+---+
| a | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
2 rows in set (0.00 sec)
mysql> select * from test1 order by b asc,a desc limit 8,2;
Empty set (0.00 sec)

Looking at the results above, the paging data is normal, and there is no data on page 5.

Summarize

order by ... [asc|desc] is used to sort the query results, asc: ascending, desc: descending, asc|desc can be omitted, the default is asc
limit is used to limit the number of rows returned by the query result. It has two parameters (offset, count). offset: indicates how many rows to skip, and count: indicates skipping the offset row and then taking the count rows.
The offset in limit can be omitted, and the default value is 0
Both offset and count in limit must be greater than or equal to 0
The values of offset and count in limit cannot be expressed as expressions.
When sorting by page, the sorting should not be ambiguous. Ambiguity may cause the paging results to be out of order. You can add a primary key sort at the end.

This is the end of this article about MySQL sorting and paging (order by & limit) and the existing pitfalls. For more relevant MySQL sorting and paging content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: