Click “SQL Database Development” above,

Set it as “top or star mark” to deliver dry goods in the first time

The ORDER BY role

The ORDER BY keyword is used to sort the result set.

The ORDER BY keyword

The ORDER BY keyword is used to sort the result set in ascending (ASC) or descending (DESC) ORDER.

The ORDER BY keyword sorts records BY ascending ORDER (ASC) BY default.

If you want to sort records in descending order, use DESC.

* * * *

The ORDER BY the grammar

SELECT column1, column2, … FROM table_name

ORDER BY column1, column2, … ASC|DESC;

Sample database

Here is the data from the “Customers” table: \

The ORDER BY instance

Select all Customers from the “Customers” table and order them by “province” column:

Example:

SELECT * FROM Customers ORDER BY province;Copy the code

Results: \

The default sort is ascending. Here the sorting rules are sorted according to the ASCII rules of the first letter of pinyin in the province. For example, the initials of Beijing are B, Guangdong is G, Hubei is H, Shanghai is S, and Zhejiang is Z. If the first letter of the order is the same, compare the second letter, and so on.

* * * *

ORDER BY DESC instance

Select all Customers from the “Customers” table and sort them in descending order by “province” :

Example:

SELECT * FROM Customers ORDER BY DESC;Copy the code

Results: \

When DESC is added, the sorting method becomes descending, and the sorting rule is the opposite of ascending. \

* * * *

ORDER BY multiple column instances

To make this easier to understand, we add two rows of data to the Customers table.

INSERT INTO `Customers`(Customer ID, name, address, city, zip code, province) VALUES (6.'a song'.'21 Huacheng Avenue'.'guangzhou'.'510000'.'Guangdong' );
INSERT INTO `Customers`(Customer ID, name, address, city, zip code, province) VALUES (7.'liu 2'.'121 Chang 'an Avenue'.'Beijing'.'100000'.'Beijing' );
Copy the code

The Customers table with added data looks like this:

Select all Customers from the “Customers” table and order them by “province” and “name” columns:

The sample

SELECT * FROM Customers ORDER BY province;Copy the code

Results: \

As you can see, when ranking provinces, Beijing and Guangdong appear two each. At this time, the alphabetic first letter of the name column is sorted. Under the premise of the same province, Liu er’s initial letter is L, and Zhao Qi’s initial letter is Z, so Liu er ranks ahead of Zhao Qi. Similarly, the first letter of Song is S, and the first letter of Wang Wu is W, so Song is in front of Wang Wu.

ORDER BY multiple column instance 2

The following SQL statement selects all Customers from the “Customers” table in ascending order by “province” and descending order by “name” column: \

SELECT * FROM Customers ORDER BY ASC;Copy the code

Results: \

When the names are sorted in descending order, the sorting results in the reverse of ascending order.

Note: If you want to sort multiple columns in descending order, you must use DESC after each column.

Combination of ORDER BY and LIMIT instances

Using LIMIT, we can find the top three customers in order, such as the top three customers in order of province:

SELECT * FROM Customers
ORDER BY ` provinces `
LIMIT 3
Copy the code

The result is:

The results show only the first three sorted customers. \

-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Why does Alibaba ban stored procedures? A very complete MySQL specification, quick collection! Why is it not recommended to use a foreign key constraint in the database over ali provisions3Select * from table where JOIN is prohibited. MySQL 解 决 方 法 : MySQL 解 决 方 法Copy the code

Click "Read original" to learn about SQL BootcampCopy the code