The ORDER BY statement in MySQL is used to sort the result set of a query in ascending or descending order based on one or more columns.
The syntax for ORDER BY statement is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
where column1, column2, … are the columns on which you want to sort the result set, and ASC and DESC are optional keywords to specify the sorting order as ascending or descending respectively. If you don’t specify any sorting order, the default is ascending.
Here are some examples:
- Sorting a table by a single column:
Suppose we have a table called customers with columns id, name, age, and country. To sort the table by the name column in ascending order, we can use the following query:
SELECT * FROM customers
ORDER BY name ASC;
This will return all the rows in the customers table sorted by the name column in ascending order.
- Sorting a table by multiple columns:
To sort a table by multiple columns, you can specify them in the ORDER BY clause separated by commas. For example, to sort the customers table by the country column in descending order and then by the name column in ascending order, you can use the following query:
SELECT * FROM customers
ORDER BY country DESC, name ASC;
This will return all the rows in the customers table sorted by the country column in descending order first, and then by the name column in ascending order within each country.
- Sorting by an expression:
You can also sort a result set by an expression, which can be a mathematical calculation, a string concatenation, or a function call. For example, to sort the customers table by the length of their names in ascending order, you can use the following query:
SELECT * FROM customers
ORDER BY LENGTH(name) ASC;
This will return all the rows in the customers table sorted by the length of their names in ascending order.
In summary, the ORDER BY statement in MySQL is a powerful tool for sorting the result set of a query in various ways.
