MySQL Order By Clause

In this article, you will learn everything about MySQL Order By Clause to sort the result st in ascending or descending order. Most of the time you need to fetch data from the database table in a specific order, the order should be ascending order or descending order, Then you can use MySQL Order By keyword to sort the result set. You can apply for the order by clause in any of the columns in table.

MySQL Order By Clause Introduction

MySQL Order By Clause is used to sort the result ( fetch by SELECT statement ) in a specific order. When you use MySQL SELECT statement to select the columns from the table, In that case, the result set is un-sorted.To sort the rows in the result set you can add the ORDER BY clause in your SELECT statement.

Syntax

This is the basic syntax of the MySQL Order By Clause statement.

SELECT select_list
 FROM table_name
 ORDER BY 
	column1 [ASC|DESC],
	column1 [ASC|DESC],
	column1 [ASC|DESC],
	..................,
	..................;

Let’s break down the above syntax.

• select_list represents the column name list that you want to select using the SELECT statement.
• table_name represents the name of the table you want to select columns from.

This ORDER BY clause sorts the result set in ascending order.

ORDER BY column1 ASC;

This ORDER BY clause sorts the result set in descending order.

ORDER BY column1 DESC;

Note:- By default ORDER BY clause sorts the result set in ascending order so the following ORDER BY clauses is the same.

ORDER BY column1 ASC;

or

ORDER BY column1;

MySQL ORDER BY Clause Examples:

To use the MySQL ORDER BY Clause with examples, we will use a students table.

We have a table named students, which has the following data.

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |
+-------+------------+-----------+--------+---------------------+
6 rows in set (0.26 sec)

a). Sort the result set by one column

Here we are going to sort the result set in the first_name column in ascending order.

Example:- MySQL Order By Asc

SELECT * FROM students ORDER BY first_name;

Output:

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |
+-------+------------+-----------+--------+---------------------+

If you want to sort the result set in ascending order by one column, you have to use the DESC keyword. Here we are going to sort the result in descending order with the last_name column.

Example: MySQL Order By Desc

SELECT first_name, last_name FROM students ORDER BY last_name DESC;

Output:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Pankaj     | Singh     |
| Vishvajit  | Rao       |
| Shivam     | Kumar     |
| Hayati     | Kaur      |
| Aysha      | Garima    |
| John       | Doe       |
+------------+-----------+
6 rows in set (0.00 sec)

b). Sort the result set by multiple columns

Here we are going to sort the result with first_name and last_name in ascending order.

Example:- MySQL ORDER By Multiple Columns

SELECT first_name, last_name FROM students ORDER BY first_name ASC, last_name ASC;

Output

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Aysha      | Garima    |
| Hayati     | Kaur      |
| John       | Doe       |
| Pankaj     | Singh     |
| Shivam     | Kumar     |
| Vishvajit  | Rao       |
+------------+-----------+
6 rows in set (0.07 sec)

If you want to sort the result set in descending order by multiple columns, you have to use the DESC keyword.
Here we are going to sort the result in descending order with first_name, last_name.

Example: MySQL order by multiple columns desc

SELECT first_name, last_name FROM students ORDER BY first_name DESC, last_name DESC;

Output:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Vishvajit  | Rao       |
| Shivam     | Kumar     |
| Pankaj     | Singh     |
| John       | Doe       |
| Hayati     | Kaur      |
| Aysha      | Garima    |
+------------+-----------+
6 rows in set (0.00 sec)

MySQL ORDER BY and NULL

In MySQL, null comes before non-values. When you are using the ORDER BY clause with the ASC keyword, Null appears first in the result set.

Example:

SELECT first_name, last_name FROM students ORDER BY first_name ASC, last_name DESC;

Output

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Ayat       | NULL      |
| Aysha      | Garima    |
| Hayati     | Kaur      |
| John       | Doe       |
| Pankaj     | Singh     |
| Shivam     | Kumar     |
| Vishvajit  | Rao       |
+------------+-----------+
6 rows in set (0.00 sec)

Recap:

• Use ORDER BY clause to sort the result by one or more columns.
• Use ASC keyword to sort the result in ascending order and use DESC to sort the result in descending order.
• In MySQL, NULL is lower than non-NULL values.

Conclusion

So, In this article, we have been seen everything about MySQL ORDER BY clause to sort the result set in ascending order and descending order. In MySQL, the ORDER BY clause is a very useful clause, especially when you want to sort your result in a specific order ( Ascending or Descending Order ).

I hope this article will help you. if you like this article, share and keep visiting for further MySQL tutorials.

Reference:- Click Here

Thanks for your valuable time …