How to use MySQL to sort query results and limit the number of returned results,

How to use MySQL to sort query results and limit the number of returned results,

Sorting of MySQL Order By query results
ORDER
The order by keyword in SQL syntax is used to sort the query results.
Sorting is divided into two types: Ascending ORDER (ASC) and descending ORDER (DESC). If order by is not used to specify the sorting method, the default value is ascending.
Syntax:

SELECT column,… FROM tb_name ORDER BY column1,column2,… DESC(ASC)

The name of the sorted field must be listed after order by. It can be multiple fields.
Perform a descending query on the uid of the user table:

SELECT uid,username FROM user ORDER BY uid DESC

The query result is as follows:

Uid username4 John 3 Jack2 James 1 Admin

Example 2:

SELECT username FROM user ORDER BY regdate DESC LIMIT 10

In this example, the username of the 10 most recently registered users is queried.

MySQL Limit limits the number of query records
MySQL LIMIT
The LIMIT keyword in MySQL is used to LIMIT the maximum number of returned query records.
Syntax:

... LIMIT offset , rows

In this syntax, offset indicates the offset (pointing to the cursor of the data record), and rows indicates the maximum number of records returned by the query. The two parameters must be integers.
Example:

SELECT username FROM user LIMIT 4,10

If there are more than 14 data records in the user table, this example will return 5th-14 records that match the results (10 in total). Note that the default offset starts from 0.
If the offset parameter is omitted, the default value is 0, that is, LIMIT 10 is equivalent to LIMIT (the first 10 records that meet the query conditions are returned ).
Prompt
In LIMIT, rows does not support Value-1 (all data from the current offset to the end of the table record), for example:

SELECT username FROM user LIMIT 9,-1

Running this SQL statement produces a parameter error.
Tips
When you confirm that there is only one data record in the query result (such as checking the user name and password), you can add the LIMIT of LIMIT 1, when the system queries a piece of data, it stops searching instead of searching for the next record, which effectively improves the query efficiency.

Articles you may be interested in:

• MySQL query optimization: Connection query sorting limit (join, order by, limit statements)
• MySQL query optimization: Connection query sorting
• Usage and precautions of limit in Mysql
• Mysql limit paging optimization method sharing