SQL Series (v)--sort (order by)

Sorting query results is the most common requirement in everyday application development, and is implemented in SQL through order by. The order by is part of the SELECT statement, which is the clause.

1.order by1.1 single-column sorting

In fact, the retrieved data is not randomly displayed. If you do not sort, the data will generally appear in the order in which it appears in the underlying table, which may be the order in which the data was originally added to the table. However, if the data is subsequently updated or deleted, the order will be affected by how the DBMS reuses the storage space.

Clause (clause)
SQL statements are made up of clauses, some are required, and others are optional. A clause is usually made up of a keyword plus the data provided. Examples of clauses that we have in the previous
The FROM clause of the SELECT statement that you see in the lesson.

To explicitly sort the data retrieved with the SELECT statement, you can use the ORDER BY clause. The ORDER BY clause takes the name of one or more columns and sorts the output accordingly.

As can be seen from the above:

• ORDER BY is a clause of select
• Options available
• can function on multiple columns

Watch out.

1. The position of the ORDER by clause:
   When you specify an ORDER BY clause, you should ensure that it is the last sentence in the SELECT statement. If it is not the last clause, an error message will appear

2. To sort by a non-select column:
   Typically, the columns used in the ORDER BY clause will be the columns that are selected for display. However, it is not necessarily true that sorting data with non-retrieved columns is completely legal

1.2 Multi-column sorting

The order by is not only useful for sorting on a single column, but it can also be used to sort multiple columns. To sort by more than one column, simply specify the column name, separating the column names with commas (as if you were selecting multiple columns).
Priority of sorting: Decrements by the column immediately following the order by.

1.3 Specifying the sort direction

Data sorting is not limited to ascending sort (from A to Z), which is just the default sort order. You can also use the ORDER BY clause to sort in descending order (from Z to a). In order to sort in descending order, you must
Specifies the DESC keyword. The default is ascending.

2 Practice 2.1 ORDER by simple use

If the query is not sorted, such as:

select * from student;

Results such as:

Age

name
Lxy	20
Son	1
Wly	18

If you are sorting:

select * from student order by age;

Results such as:

Age

name
Son	1
Wly	18
Lxy	20

Sort from large to small according to age, that is, sort in ascending order.

Then look at the columns that are not retrieved:

select name from student order by age;

Results such as:

Name
Son
Wly
Lxy

The retrieved results are maintained in the same order as above.

2.2 Multi-column sorting practice

select * from student;

The results are as follows:

Age

name
Lxy	20
Lxy	10
Lxy	25
Son	1
Wly	18
Wly	16

If you are sorting by Name,age:

select * from student order by name, age;

The results are as follows:

Age

name
Lxy	10
Lxy	20
Lxy	25
Son	1
Wly	16
Wly	18

The priority can be seen from the two results above. Sorts the results by name first, and then the results of the same name, sorted by age.

2.3 Specifying the sort direction

select * from student order by name;select * from student order by name asc;

Two results of the above query are consistent:

Age

name
Lxy	20
Lxy	10
Lxy	25
Son	1
Wly	18
Wly	16

If you sort in descending order:

select * from student order by name desc;

The results are as follows:

Age

name
Wly	18
Wly	16
Son	1
Lxy	20
Lxy	10
Lxy	25

Reference

"SQL must know must be"

SQL Series (v)--sort (order by)