In this tutorial, you'll learn how to use the MySQL
ORDER BY clause to sort the rows in a result set.
The MySQL ORDER BY clause: An overview
The order of rows returned by a SELECT statement query from a table is undefined. Inserting the
ORDER BY clause after the
SELECT statement will sort the rows of the result set in the order specified.
The syntax of the
ORDER BY clause is shown below:
SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
In the above
ORDER BY clause syntax, you name the column(s) that will be used to determine the sort order.
The ASC indicates ascending order which is the default order, whereas the DESC indicates a descending one. Ascending (ASC) and descending (DESCENDING) are used to sort the results.
ORDER BY clause arranges the output in ascending order of the values in column1:
ORDER BY column1 ASC;
Since there is no inherent order between rows in a relational database, we must instruct MySQL to sort the results before we can use them in any meaningful way.
MySQL ORDER BY Clause Examples
It's possible you'd like to get back an index of all the customers in some customer database, ordered by their customer names. Here is what you would enter:
SELECT customer_id, first_name, last_name FROM Customers order by first_name;
The above result is in ascending order which is the default order for the
ORDER BY clause.
To get the same result in descending order, we will specify the SQL query as follows:
SELECT customer_id, first_name, last_name FROM Customers order by first_name DESC;
Now our result is in descending order for the column
first_name. But the
last_name column is not in order. So if you want to sort both columns (
first_name, last_name) in descending order you will have to specify the
DESC clause after each column name separated by a comma. Below is an example:
SELECT customer_id, first_name, last_name FROM Customers order by first_name desc, last_name desc
We have the age column in the customers table. So now we will sort the result on the age colunm which is a numeric column. Below is the example:
select * from customers order by age;
But in the above result set, you can see that the row with the null value of the age column is displayed first. So if you want to sort the result but want the null value in last, use the
nulls last keyword after the column name. The following is an example:
select * from customers order by age nulls last;
Points to Remember for ORDER BY Clause
- Sort the output by one or more columns using the
- For an ascending sort, select
ASCand a descending sort, select
SELECTclauses are examined before the
ORDER BYclause is considered.
- MySQL ranks
NULLas a lesser value than anything else.
In this tutorial, you have learned MySQL
ORDER BY clause to sort the table data returned by the