Database - sorting data

This is the third literature following the above literature , This document is about the literature summary of sorting data in the database , Use SELECT When querying data from a table , The row order in the result set is not guaranteed . It means SQL Server You can return a result set with unspecified row order . The way to ensure that the rows in the result set are sorted is to use ORER BY Clause . Here are ORDER BY Syntax of clause ：

In the above grammar ,column_name | expression - Specify the column name or expression to sort the query result set . If more than one column is specified , Sort by the first column of the result set , The sorting result set is then sorted by the second column , And so on .ORDER BY The columns appearing in the clause must correspond to the columns in the selection list or FROM Columns defined in the table specified in clause .

ASC | DESC - Use ASC or DESC Specifies whether the values in the specified column should be sorted in ascending or descending order . ASC Sort the results from the lowest value to the highest value , and DESC Sort the result set from the highest value to the lowest value . If you do not specify ASC or DESC , be SQL Server Use by default ASC To sort order . Besides ,SQL Server take NULL Regarded as the lowest value .

Processing has ORDER BY Clause SELECT When the sentence is , ORDER BY Clause is the last clause to be processed . Sort the result set in ascending order. The following statement sorts the customer list in ascending order by name ： Sort the result set in descending order by one column. The following statement sorts the customer list in descending order by name . Sort the result set by multiple columns. The following statement retrieves the customer's name , Last name and city . It first sorts the list of customers by city , Then sort by name .

Sort the result set by multiple columns and in different order. The following statement sorts customers in descending order by city , Then sort the sorting results in ascending order according to the first name . Sort the result set by columns that are not in the selection list , Be careful , state Listed in customers It is defined in the table . If not , Then the query will be invalid . You can sort the result set by selecting columns that are not displayed in the list . for example , The following statement is as follows state Sort customers , Even if state The column is not displayed in the selection list .

Sort the result set by expression ,LEN() Function returns the number of characters in a string . The following sentence uses ORDER BY In Clause LEN() Function to retrieve a list of customers sorted by name length . Sort by the ordinal position of the column SQL Server Allows you to sort the result set according to the ordinal position of the columns displayed in the selection list . The following statement sorts customers by first name and last name . But it doesn't explicitly specify the column name , Instead, use the ordinal position of the column ：