SQL Server: Reading notes from the book of "The Must-know-all" (iii)

3rd class sorting Retrieve data

3.1 Sorting data
The following SQL statement returns a single column for a database table. Observe its output, and there is no specific order.

SELECT Prod_name  from Products;

Hint relational database design theory argues that if the sort order is not explicitly specified, the order of the retrieved data should not be assumed to have any meaning.

In order to explicitly retrieve the data order, the ORDER BY clause can be used to take the name of one or more columns, and the output is sorted accordingly.

SELECT Prod_name  from  Products ORDER  by Prod_name; --     Analysis: It is different from the previous statement, except that the Prod_name column is arranged in alphabetical order

3.2 Sorting by multiple columns
To sort by more than one column, simply specify the column name, separating the column names by commas

The following code retrieves 3 columns and sorts the results by two columns--first by Price, then by name

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by Prod_price, Prod_name;

3.3 Sorting by column position
In addition to being able to indicate the sort order with a column name, order by also supports sorting by relative column position.

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by 2 3 ; --     The output here is the same as the above query. 2 finger prod_price,3 finger prod_name

3.4 Specifying the sort direction
Data sorting is sorted by default in ascending order, and you can specify the DESC keyword to sort it in descending order.

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by DESC -- The DESC keyword is applied only to the column name immediately preceding it. 

What if I want to sort by more than one column?

The following example sorts products in descending order (most expensive at the front), plus the product name:

SELECT prod_id, Prod_price, Prod_name  from  Products ORDER  by DESC , Prod_name -- parse: The DESC keyword is applied only to the column name immediately preceding it. 

Note If you want to sort descending on more than one column, you must specify the DESC keyword for each column

SQL Server: Reading notes from the book of "The Must-know-all" (iii)