When it comes to retrieving data from a MySQL database, the Order By clause is an essential tool that every developer should have in their arsenal. It allows you to sort the data in the result set in ascending or descending order based on one or more columns. This article will discuss everything you need to know about the MySQL Order By clause, from its syntax to practical examples of how to use it effectively.
Syntax of the MySQL Order By Clause
The Order By clause is used in a SELECT statement to sort the result set by one or more columns. Its syntax is as follows:
SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;Here, you specify the columns you want to retrieve data from in the SELECT statement, followed by the ORDER BY clause. You then specify the column or columns you want to sort the data by, followed by either ASC or DESC to indicate whether the sort should be in ascending or descending order, respectively. You can sort the data by one or more columns, in any order.
Basic Example of MySQL Order By
To better understand how the MySQL Order By clause works, let’s start with a basic example. Suppose we have a table called users with the following data:
+----+-------+--------+------------+
| id | name | gender | birth_date |
+----+-------+--------+------------+
| 1 | John | Male | 1990-05-01 |
| 2 | Sarah | Female | 1995-02-14 |
| 3 | Mark | Male | 1998-11-21 |
| 4 | Jane | Female | 1985-07-08 |
+----+-------+--------+------------+To retrieve the data from the users table sorted by the name column in ascending order, we would use the following query:
SELECT * FROM users ORDER BY name ASC;This would give us the following result set:
+----+-------+--------+------------+
| id | name | gender | birth_date |
+----+-------+--------+------------+
| 1 | John | Male | 1990-05-01 |
| 4 | Jane | Female | 1985-07-08 |
| 3 | Mark | Male | 1998-11-21 |
| 2 | Sarah | Female | 1995-02-14 |
+----+-------+--------+------------+We can see that the data has been sorted by the name column in ascending order, as specified in the query.
Sorting by Multiple Columns
The MySQL Order By clause allows you to sort the data by multiple columns, in any order. Let’s look at an example to illustrate this. Suppose we have a table called employees with the following data:
+----+------------+----------+-----------+-----------+
| id | first_name | last_name| birthdate | salary |
+----+------------+----------+-----------+-----------+
| 1 | John | Doe | 1980-01-01| 50000.00 |
| 2 | Jane | Smith | 1985-03-15| 65000.00 |
| 3 | John | Smith | 1978-05-20| 70000.00 |
| 4 | Jane | Doe | 1990-08-05| 55000.00 |
+----+------------+----------+-----------+-----------+To retrieve the data from the employees table sorted first by the last_name column in ascending order, and then by the first_name column in descending order, we would use the following query:
SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;This would give us the following result set:
+----+------------+----------+-----------+----------+
| id | first_name | last_name| birthdate | salary |
+----+------------+----------+-----------+----------+
| 4 | Jane | Doe | 1990-08-05| 55000.00 |
| 2 | Jane | Smith | 1985-03-15| 65000.00 |
| 3 | John | Smith | 1978-05-20| 70000.00 |
| 1 | John | Doe | 1980-01-01| 50000.00 |
+----+------------+----------+-----------+----------+We can see that the data has been sorted first by the last_name column in ascending order, and then by the first_name column in descending order, as specified in the query.
Sorting by Calculated Columns
In addition to sorting by columns in the table, the MySQL Order By clause also allows you to sort by calculated columns. A calculated column is a column that is not physically stored in the table, but is calculated at run-time based on an expression involving one or more columns. Let’s look at an example to illustrate this. Suppose we have a table called orders with the following data:
+----+---------+-------+-------+
| id | product | price | units |
+----+---------+-------+-------+
| 1 | Widget | 10.00 | 5 |
| 2 | Gizmo | 15.00 | 3 |
| 3 | Widget | 10.00 | 8 |
| 4 | Doodad | 25.00 | 2 |
+----+---------+-------+-------+To retrieve the data from the orders table sorted by the total price of each order (i.e., price multiplied by units), we would use the following query:
SELECT *, price * units AS total FROM orders ORDER BY total DESC;This would give us the following result set:
+----+---------+-------+-------+-------+
| id | product | price | units | total |
+----+---------+-------+-------+-------+
| 4 | Doodad | 25.00 | 2 | 50.00|
| 3 | Widget | 10.00 | 8 | 80.00|
| 2 | Gizmo | 15.00 | 3 | 45.00|
| 1 | Widget | 10.00 | 5 | 50.00|
+----+---------+-------+-------+-------+We can see that the data has been sorted by the calculated column total in descending order, as specified in the query.
Conclusion
In this article, we have covered the basics of the MySQL Order By clause, including its syntax and examples of how to use it effectively. We have seen how to sort data by one or more columns, in ascending or descending order, as well as how to sort by calculated columns. Remember that the Order By clause can significantly enhance the power and flexibility of your database queries, so make sure to master it for your MySQL development needs.
📕 Related articles about MySQL
- How to Use Alter Table – Add Statement in SQL
- How to use USING Clause in SQL
- How to use Arithmetic Operators in SQL
- How to use Inserting into MySQL database
- MySQL Connecting to Server: A Comprehensive Guide
- MySQL UPDATE Statement: How to Update Your Data in a Database
