Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the usage of ORDER BY. Many people may not know much about it. In order to let you know more, Xiaobian summarized the following contents for you. I hope you can gain something according to this article.

When the small partners are doing SQL sorting, they can naturally use ORDER BY. Whether it is the ascending order of ASC or the descending order of DESC, it is almost at your fingertips.

Today I want to share with you some ORDER BY usages that you may not know.

ORDER BY returns cursors rather than collections.

SQL theory is actually set theory, common similar to the intersection of data, union, difference set can be used to solve the set of thinking.

There is no predefined order between rows in a collection; it is just a logical combination of members, and the order between members does not matter.

As shown below, each parenthesis is a record, before sorting, they are randomly distributed in the set.

Student(ID,Name,Age)

Student set

But for queries with an ORDER BY clause that sorts, it returns an object in which rows are organized in a particular order. We call this object a cursor.

As shown in the figure below, after ordering the ID of the Student table by ORDER BY, the Student table becomes an ordered object, which is what we said above.

Student(ID,Name,Age)

Student object

The ORDER BY clause is the only step that can reuse column aliases.

Note: markdown The syntax of the code block is to add both the beginning and the end lines: ``, where `is the upper left corner of the windows keyboard

This involves the syntax order and execution order of SQL statements. Our common SQL syntax order is as follows:

SELECT DISTINCT FROM [left_table] JOIN ON WHERE GROUP BY WITH HAVING ORDER BY

The database engine does not execute SQL statements from SELECT, but from FROM. The specific execution order is as follows (the number in front of the keyword represents the order of SQL execution):

(8)SELECT (9)DISTINCT (11) (1)FROM [left_table] (3) JOIN (2) ON (4)WHERE (5)GROUP BY (6)WITH (7)HAVING (10)ORDER BY

From the above you can see that SELECT does not start until after HAVING. At this time, the aliases of the columns after SELECT are valid only for the subsequent steps, but are invalid for the previous steps of SELECT. So if you use a column alias after WHERE, GROUP BY, or HAVING, you will get an error.

Let's test it with an example.

The sample table Customers structure and data are as follows:

1. Where is not followed by alias

SELECT Name AS Name, Address AS Address, City AS City FROM Customers WHERE City ='Guangzhou'

The results were as follows:

2. Use column aliases after WHERE

SELECT AS Name, AS Address, AS City FROM Customers WHERE City='Guangzhou'

The results are as follows:

From the returned message, we can see that the renamed City cannot be identified by WHERE, so the prompt "column name 'City' is invalid" will be reported.

Other keywords can also be tested using the above method, below we test the use of column aliases after GROUP BY and HAVING.

3. Use column aliases after testing GROUP BY

SELECT AS City FROM Customers GROUP BY City

The results were as follows:

4. Use column aliases after testing HAVING

SELECT AS City FROM Customers GROUP BY CITY HAVING COUNT(City)>1

The results were as follows:

5. Use column aliases after test ORDER BY

SELECT Name AS Name, Address AS Address, City AS City FROM Customers ORDER BY City

The results were as follows:

From the results of the test examples above, we can conclude that our conclusion is correct: the ORDER BY clause is the only step where column aliases can be reused.

Third, carefully use ORDER BY followed by numbers to sort

Some friends like to write numbers after ORDER BY in order to save trouble. The specific examples are as follows:

SELECT AS Name, AS Address, AS City FROM Customers ORDER BY 1,2,3

The results were as follows:

The result of this writing is correct and no problem for the current query. The numbers 1, 2 and 3 after ORDER BY represent the first, second and third fields after SELECT (i.e. Name, Address and City) respectively.

However, when the query column changes, forget to modify the ORDER BY list. Especially when the query statement is long, it can be difficult to find which column in the SELECT list ORDER BY corresponds to.

For example:

SELECT Customer ID AS ID, Name AS Name, Address AS Address, City AS City FROM Customers ORDER BY 1,2,3

Due to the addition of a column of "Customer ID", the original title is still sorting Name, Address, City, but because ORDER BY is used plus numbers, the sorted results are as follows:

The result is not what we want, so please use ORDER BY with numbers carefully, try to use ORDER BY with column names or column aliases.

IV. Expressions cannot be sorted by ORDER BY

Table expressions include views, inline table-valued functions, derived tables (subqueries), and common table expressions (CTEs).

For example, the following view is invalid

CREATE VIEW V_Customers AS SELECT AS ID, AS Name, AS Address, AS City FROM Customers BY ORDER ID,Name,Address

The results were as follows:

Is this mistake familiar? Because many small partners often like to add ORDER BY in the view or subquery, and then this error will be reported as soon as it is executed.

The root cause dare not be determined, because search a lot of literature also did not give a specific statement.

Here I guess it is because the results returned by views, inline table-valued functions, derived tables (subqueries) and common table expressions (CTEs) need to be further used. Adding ORDER BY for sorting is superfluous, but it will waste system resources. So the developers of the database don't want people to use this kind of irregular operation.

So next time don't add ORDER BY to the table expression.

Fifth, T-SQL table expression added TOP can use ORDER BY

We can see from the error message in the fourth point that TOP, OFFSET or FOR XML can be used ORDER BY.

And why is that?

Let's start with a chestnut.

SELECT Customer ID AS ID, Name AS Name, Address AS Address, City AS City FROM (SELECT TOP 3 * FROM Customers ORDER BY CITY) Customers ORDER BY ID,Name,Address

The results were as follows:

Because T-SQL table expression with ORDER BY plus TOP returns a table without fixed order. Therefore, in this case, the ORDER BY clause simply defines the logical order for the TOP options, which is the logical clause below

SELECT TOP 3 * FROM Customers ORDER BY City

The results were as follows:

There is no guarantee of the order of the result set, because there is at least one layer outside the table expression that is the final result set we need.

ORDER BY here only works for the current subquery, not for the main query. An ORDER BY clause must be added at the end of the main query to take effect on the result set, as in our example.

Unless logically required, it is generally not recommended that you avoid the restriction that you cannot use ORDER BY in subqueries.

After reading the above, do you have any further understanding of the usage of ORDER BY? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.