What is SQL ORDER BY Clause?

 The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Summary: This tutorial shows you how to use the SQL ORDER BY clause to sort the result set based on specified criteria in ascending or descending orders.

SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

The basic syntax of the ORDER BY clause is as follows −

Syntax to sort the records in ascending order:

SELECT ColumnName1,...,ColumnNameN FROM TableName  ORDER BY ColumnName ASC;    

Syntax to sort the records in descending order:

SELECT ColumnName1,...,ColumnNameN FROM TableName  ORDER BY ColumnName DESC;    

Let us explore more on this topic with the help of examples. We will use the MySQL database for writing the queries in examples.

Consider we have customers table with the following records:

ID	NAME	AGE	ADDRESS	SALARY
2	Shiva Tiwari	22	Bhopal	21000
3	Ajeet Bhargav	45	Meerut	65000
4	Ritesh Yadav	36	Azamgarh	26000
5	Balwant Singh	45	Varanasi	36000
6	Mahesh Sharma	26	Mathura	22000
7	Rohit Shrivastav	19	Ahemdabad	38000
8	Neeru Sharma	29	Pune	40000
9	Aakash Yadav	32	Mumbai	43500
10	Sahil Sheikh	35	Aurangabad	68800

Example 1:

Write a query to sort the records in the ascending order of the customer names stored in the customers table.

Query:

mysql> SELECT *FROM customers ORDER BY Name ASC;  

Here in a SELECT query, an ORDER BY clause is applied on the column 'Name' to sort the records. ASC keyword will sort the records in ascending order.

ID	NAME	AGE	ADDRESS	SALARY
9	Aakash Yadav	32	Mumbai	43500
3	Ajeet Bhargav	45	Meerut	65000
5	Balwant Singh	45	Varanasi	36000
1	Himani Gupta	21	Modinagar	22000
6	Mahesh Sharma	26	Mathura	22000
8	Neeru Sharma	29	Pune	40000
4	Ritesh Yadav	36	Azamgarh	26000
7	Rohit Shrivastav	19	Ahemdabad	38000
10	Sahil Sheikh	35	Aurangabad	68800
2	Shiva Tiwari	22	Bhopal	21000

All the records present in the customers table are displayed in the ascending order of the customer's name.

Example 2:

Write a query to sort the records in the descending order of the customer age stored in the customers table.

Query:

mysql> SELECT *FROM customers ORDER BY Age DESC;  

Here in a SELECT query, an ORDER BY clause is applied on the column 'Age' to sort the records. DESC keyword will sort the records in descending order.

You will get the following output:

ID	NAME	AGE	ADDRESS	SALARY
3	Ajeet Bhargav	45	Meerut	65000
5	Balwant Singh	45	Varanasi	36000
4	Ritesh Yadav	36	Azamgarh	26000
10	Sahil Sheikh	35	Aurangabad	68800
9	Aakash Yadav	32	Mumbai	43500
8	Neeru Sharma	29	Pune	40000
6	Mahesh Sharma	26	Mathura	22000
2	Shiva Tiwari	22	Bhopal	21000
1	Himani Gupta	21	Modinagar	22000
7	Rohit Shrivastav	19	Ahemdabad	38000

All the records present in the customers table are displayed in the descending order of the customer's age.