ORDER BY Clause MySQL Essential Training

ORDER BY Clause MySQL Essential Training

MySQL

ORDER BY Clause MySQL Essential Training




In this ORDER BY Clause MySQL Essential Training lesson will teaches you how to sort a result set using MySQL Order By Clause. Already we used this clause in our previous example we didn’t know about its full power. So this lesson will be the chance to explain the functionalities of the ORDER BY Clause. Lets Jump into the topic with real time example.
ORDER BY Clause MySQL Essential Training – Introduction
As usual whenever we select a result set using the SELECT statement, the result is not sorted by any order. It shows the rows the order as it is. If we want some standard result then we go with the order by clause. It can sort the result set in two methods, like the following
Sort a result set by a single column or multiple columns.
Sort a result set by different columns in ascending or descending order.
The following is the syntax for the ORDER BY clause.

Here we gives the two attributes or the parameters to the query. Where the ASC is describes the Ascending as like as the DESC is explains the Descending. The default sorting order in MySQL is the ascending order if we didn’t mention it manually.
ORDER BY Clause MySQL Essential Training – Live Example
Lets consider the products table for this example to explain you as clearly the performance of the ORDER BY Clause MySQL Essential Training.

ORDER BY Clause MySQL Essential Training




In the above example we can see, the result is sorted based on the product_name and not the product_category which is given as first. Then in the next time we just want to display that list in a reverse order, now we use the order by clause and use desc parameter.

ORDER BY Clause MySQL Essential Training
In the above both examples, if we did ascending or descending it affects the both columns in the table products. But in some time we wants to display the product_category in ASC order. And vice versa we wants to sort the product name by DESC order like we did it in the following example.

ORDER BY Clause MySQL Essential Training
In ORDER BY Clause MySQL Essential Training lesson, we can use MySQL ORDER BY sort by in an expression example. The ORDER BY clause also allows you to sort the result set based on an expression.
This is also perform the query based on the expressions and calculates the values on the run time it self. For example here we calculate the total price value of each line and sorts the result set based on product_name, Product_category and subtotal. Consider the following example for the clear understanding.

ORDER BY Clause MySQL Essential Training
To make the query more readable, you can sort by the column alias as the following query:

ORDER BY Clause MySQL Essential Training




MySQL ORDER BY with customer sort order – Custom Sort Order
The Order By clause gives us a chance to enable the custom sort order for the values in a column using a FIELD() function.
For example, if you want to sort the orders based on the following status by the following order:
In Process
On Hold
Cancelled
Resolved
Disputed
Shipped
You can use the FIELD function to map those values to a list of numeric values and use the numbers for sorting; See the following query:

ORDER BY Clause MySQL Essential Training
In this lesson we hope this gives a great experience on ORDER BY clause and also the AS clause additionally to describe the alias function.




Leave a Reply