MySQL must know-5th chapter-Sort the retrieved data

The fifth chapter sort retrieves the data

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

5.1 Sorting Data

As described in the previous chapter, the following SQL statement returns a single column for a database table. But look at its output, and there is no specific order.

In fact, the retrieved data is not displayed in a purely random order. If not sorted, the data is typically displayed in the order in which it appears in the underlying table. This can be the order in which the data was initially added to the table. However, if the data is later updated or deleted, this order will be affected by the MySQL reuse reclaim storage space. Therefore, if you do not explicitly control it, you cannot (and should not) rely on the sort order. 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 be meaningful.

Clause (clause) SQL statement consists of clauses that are required, and some are optional. A clause is usually made up of a keyword and the data provided. The example of the clause has the FROM clause of the SELECT statement, which we have seen in the previous chapter. 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. Take a look at the following example:

This statement is the same as the previous statement except that it instructs MySQL to sort the prod_name column alphabetically by the ORDER BY clause of the data.
Sort by non-select columns 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.

5.2 Sorting by multiple columns

It is often necessary to sort data by more than one column. For example, if you want to display an employee list, you might want to sort by last name and first name, sorted by last name, and then by name in each last name. This is useful if multiple employees have the same last name. To sort by more than one column, the column names are separated by commas (as you do when you select multiple columns), as long as you specify the column name. The following code retrieves 3 columns and sorts the results by two columns-first by price, then by name.

It is important to understand that when sorting by more than one column, the sorting takes place exactly in the order specified. In other words, for the output in the above example, 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.

5.3 Specifying the sort direction

Data sorting is not limited to ascending sort (from A to Z). This is just the default sort order, and you can use the ORDER BY clause to sort in descending (Z to a). To sort in descending order, you must specify the DESC keyword. The following example sorts products by price in descending order (most expensive first):

But what if I want to sort multiple columns? The following example sorts products in descending order (most expensive at the front), and then sorts the product names:

The DESC keyword is applied only to the column name immediately preceding it. In the example above, DESC is specified only for the Prod_price column, not for the Prod_name column. Therefore, the Prod_price column is sorted in descending order, and 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 descending on more than one column, you must specify the DESC keyword for each column. The keyword opposite to desc is ASC (ascending), which can be specified in ascending order. In practice, however, ASC is not of much use, because ascending is the default (if neither ASC nor DESC is specified, ASC is assumed).
Is the case-sensitive and sort order the same as a and a when the textual data is sorted? is a before B or after Z? These questions are not theoretical, and the answer depends on how the database is set up. In the Dictionary (dictionary) sort order, a is considered to be the same as a, which is the default behavior of MySQL (and most database management systems). However, many database administrators can change this behavior if needed (you might have to do this if your database contains a large number of foreign language characters). The key question here is that if you really need to change this sort order, you can't do it with a simple order BY clause. You must ask the database administrator for help.
Use the combination of order by and limit to find the highest or lowest value in a column. The following example shows how to find the value of the most expensive item:

The Prod_price desc guarantee line is retrieved from the most expensive to cheapest, while the limit 1 tells MySQL to return only one row.
The position of the ORDER BY clause, when given an ORDER BY clause, should be guaranteed to be located after 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.

5.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.

MySQL must know-5th chapter-Sort the retrieved data