4. mysql must know the sort retrieved data-order by

Tag: Statement ' img ' tells to generate IMA column sort message strong

This chapter teaches you how to use the ORDER BY clause of a SELECT statement to sort the retrieved data as needed.

1. Sorting Data

?? clause (clause) SQL statements are made up of clauses, some of which are mandatory, and others are optional. A clause is usually made up of a keyword and the data provided. The example of a clause has a FROM clause of the SELECT statement.

To explicitly sort the data retrieved with the SELECT statement, you can sort the output by using the ORDER BY clause to take the name of one or more columns.

Take a look at the following example:

input :

SELECT  prod_numFROM productsORDER BY prod_num

Output :

Analysis: This statement instructs MySQL to sort data in numerical order on the Prod_num column.

?? sorting by a non-select column

Typically, the columns used in the ORDER BY clause will be displayed for the selected column. However, it is not necessarily true that sorting data with non-retrieved columns is completely legal.

2. Sort by multiple columns

input :

SELECT  prod_id, prod_price, prod_name FROM    productsORDER  BY   prod_price,  prod_name

Output:

prod_id	Prod_price	Prod_name
Fc	2.50	Carrots
TNT1	2.50	TNT (1 stick)
FU1	3.42	Fuses
SLING	4.49	Sling

Analysis : Retrieve 3 columns in the code above and sort the results by two columns---first price, then sort by name.

?? It is important to understand that when sorting by multiple columns, the sort is done exactly as specified . For the example above, the product is sorted by Prod_name only if more than one row has the same prod_price value. If all the values in the Prod_price column are unique, they are not sorted by Prod_name.

3. Specify 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 by descending (from Z to a).

Keyword: DESC

Sort products by Price in descending order (most expensive in front)

input :
```
SELECT prod_id, Prod_price, Pro_name
From Producs
ORDER by Prod_price DESC;

```

If you want to sort by more than one column, the following example sorts the products in descending order (most expensive at the front), and then sorts the product names:

Input:

SELECT prod_id, prod_price, prod_nameFROM productsORDER BY  prod_price DESC, pro_name

Analysis: The DESC keyword is applied only to the column name immediately preceding it. In the example above, only the Prod_price column is specified with the DESC column sorted in descending order, while the Prod_name column (within each price) is still sorted in ascending order by standard.

?? Sort Descending on multiple columns

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

Using the combination of order by and limit, you can find the highest or lowest value in a column.

Find the highest value:
input :

SELECT prod_price FROM    products ORDER  BY  prod_price DESC LIMIT 1

Output :

Prod_price |
---|
55.00|

Analysis: the Prod_price DESC guarantee line is based on the most expensive to cheapest search, while limit 1 tells MySQL to return only one row.

?? the position of the ORDER by clause

When the ORDER BY clause is given, it should be guaranteed to be behind the FROM clause. If limit is used, it must be after order by. Using clauses in the wrong order will result in an error message.

4. Summary

This chapter learns how to sort the retrieved data with the ORDER BY clause of the SELECT statement. This clause must be the last one in the SELECT statement. You can use it to sort data on one or more columns, as needed.

4. mysql must know the sort retrieved data-order by