SQL ORDER BY CLAUSE WITH ASCENDING ORDER

  • Whenever we want to sort the records based on the columns stored in the tables of the SQL database, then we consider using the ORDER BY clause in SQL.
  • The ORDER BY clause in SQL helps us sort the records based on a table's specific column. This means that initially, all the values stored in the column on which we are applying the ORDER BY clause will be sorted. Then the corresponding column values will be displayed in the same sequence in which the values we have obtained in the earlier step.
  • Using the ORDER BY clause, we can sort the records in ascending or descending order as per our requirement. The records will be sorted in ascending order whenever the ASC keyword is used with the ORDER by clause. Whereas, DESC keyword will sort the records in descending order. If no keyword is specified after the column based on which we have to sort the records, then in that case, the sorting will be done by default in the ascending order.

Before writing the queries for sorting the records, let us understand the syntax.

Syntax to sort the records in ascending order:

Syntax to sort the records in ascending order without using ASC keyword:

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:

IDNAMEAGEADDRESSSALARY
1Himani Gupta21Modinagar22000
2Shiva Tiwari22Bhopal21000
3Ajeet Bhargav45Meerut65000
4Ritesh Yadav36Azamgarh26000
5Balwant Singh45Varanasi36000
6Mahesh Sharma26Mathura22000
7Rohit Shrivastav19Ahemdabad38000
8Neeru Sharma29Pune40000
9Aakash Yadav32Mumbai43500
10Sahil Sheikh35Aurangabad68800

Example 1:

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

Query:

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.

You will get the following output:

IDNameAgeAddressSalary
9Aakash Yadav32Mumbai43500
3Ajeet Bhargav45Meerut65000
5Balwant Singh45Varanasi36000
1Himani Gupta21Modinagar22000
6Mahesh Sharma26Mathura22000
8Neeru Sharma29Pune40000
4Ritesh Yadav36Azamgarh26000
7Rohit Shrivastav19Ahemdabad38000
10Sahil Sheikh35Aurangabad68800
2Shiva Tiwari22Bhopal21000

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 ascending order of the addresses stored in the customers table.

Query:

Here in a SELECT query, an ORDER BY clause is applied to the 'Address' column to sort the records. No keyword is used after the ORDER BY clause. Hence, the records, by default, will be sorted in ascending order.

You will get the following output:

IDNameAgeAddressSalary
7Rohit Shrivastav19Ahemdabad38000
10Sahil Sheikh35Aurangabad68800
4Ritesh Yadav36Azamgarh26000
2Shiva Tiwari22Bhopal21000
6Mahesh Sharma26Mathura22000
3Ajeet Bhargav45Meerut65000
1Himani Gupta21Modinagar22000
9Aakash Yadav32Mumbai43500
8Neeru Sharma29Pune40000
5Balwant Singh45Varanasi36000

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

Example 3:

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

Query:

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

You will get the following output:

IDNameAgeAddressSalary
2Shiva Tiwari22Bhopal21000
1Himani Gupta21Modinagar22000
6Mahesh Sharma26Mathura22000
4Ritesh Yadav36Azamgarh26000
5Balwant Singh45Varanasi36000
7Rohit Shrivastav19Ahemdabad38000
8Neeru Sharma29Pune40000
9Aakash Yadav32Mumbai43500
3Ajeet Bhargav45Meerut65000
10Sahil Sheikh35Aurangabad68800

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

Example 4:

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

Query:

Here in a SELECT query, an ORDER BY clause is applied on the column 'Age' to sort the records. No keyword is used after the ORDER BY clause. Hence, the records, by default, will be sorted in ascending order.

You will get the following output:

IDNameAgeAddressSalary
7Rohit Shrivastav19Ahemdabad38000
1Himani Gupta21Modinagar22000
2Shiva Tiwari22Bhopal21000
6Mahesh Sharma26Mathura22000
8Neeru Sharma29Pune40000
9Aakash Yadav32Mumbai43500
10Sahil Sheikh35Aurangabad68800
4Ritesh Yadav36Azamgarh26000
5Balwant Singh45Varanasi36000
3Ajeet Bhargav45Meerut65000

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

Consider we have another table named agents with the following records:

AIDNameWorkAreaProfit_PercentContactNumberSalary
1Gurpreet SinghBangalore1998967543243000
2Sakshi KumariChennai5819056734225000
3Prachi DesaiMumbai2905612343260000
4Shivani MorePune3889423678935500
5Pallavi SinghDelhi4779809234138700
6Rohini KulkarniAmbala8789094561225670
7Shweta DixitChandigarh6889878645331670
8Sonakshi TiwariUdaipur2980945342125050
9Anushka TripathiUjjain9890912432638000
10Devika SharmaGoa7786452314544050

Example 1:

Write a query to sort the records in the ascending order of the agent names stored in the agents table.

Query:

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.

You will get the following output:

AIDNameWorkAreaProfit_PercentContactNumberSalary
9Anushka TripathiUjjain9890912432638000
10Devika SharmaGoa7786452314544050
1Gurpreet SinghBangalore1998967543243000
5Pallavi SinghDelhi4779809234138700
3Prachi DesaiMumbai2905612343260000
6Rohini KulkarniAmbala8789094561225670
2Sakshi KumariChennai5819056734225000
4Shivani MorePune3889423678935500
7Shweta DixitChandigarh6889878645331670
8Sonakshi TiwariUdaipur2980945342125050

All the records present in the agents table are displayed in the ascending order of the agent's name.

Example 2:

Write a query to sort the records in the ascending order of the agent salary stored in the agents table.

Query:

Here in a SELECT query, an ORDER BY clause is applied on the column 'Salary' to sort the records. No keyword is used after the ORDER BY clause. Hence, the records, by default, will be sorted in ascending order.

You will get the following output:

AIDNameWorkAreaProfit_PercentContactNumberSalary
2Sakshi KumariChennai5819056734225000
8Sonakshi TiwariUdaipur2980945342125050
6Rohini KulkarniAmbala8789094561225670
7Shweta DixitChandigarh6889878645331670
4Shivani MorePune3889423678935500
9Anushka TripathiUjjain9890912432638000
5Pallavi SinghDelhi4779809234138700
1Gurpreet SinghBangalore1998967543243000
10Devika SharmaGoa7786452314544050
3Prachi DesaiMumbai2905612343260000

All the records present in the agents table are displayed in the ascending order of the customer's salary.

Example 3:

Write a query to sort the records in the agent's work area's ascending order stored in the agent's table.

Query:

Here in a SELECT query, an ORDER BY clause is applied on the column 'WorkArea' to sort the records. No keyword is used after the ORDER BY clause. Hence, the records, by default, will be sorted in ascending order.

You will get the following output:

AIDNameWorkAreaProfit_PercentContactNumberSalary
6Rohini KulkarniAmbala8789094561225670
1Gurpreet SinghBangalore1998967543243000
7Shweta DixitChandigarh6889878645331670
2Sakshi KumariChennai5819056734225000
5Pallavi SinghDelhi4779809234138700
10Devika SharmaGoa7786452314544050
3Prachi DesaiMumbai2905612343260000
4Shivani MorePune3889423678935500
8Sonakshi TiwariUdaipur2980945342125050
9Anushka TripathiUjjain9890912432638000

All the records present in the agents table are displayed in the ascending order of the customer's work area.

Example 4:

Write a query to sort the records in the ascending order of the agent's profit percentage stored in the agents table.

Query:

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

You will get the following output:

AIDNameWorkAreaProfit_PercentContactNumberSalary
1Gurpreet SinghBangalore1998967543243000
3Prachi DesaiMumbai2905612343260000
8Sonakshi TiwariUdaipur2980945342125050
4Shivani MorePune3889423678935500
5Pallavi SinghDelhi4779809234138700
2Sakshi KumariChennai5819056734225000
7Shweta DixitChandigarh6889878645331670
10Devika SharmaGoa7786452314544050
6Rohini KulkarniAmbala8789094561225670
9Anushka TripathiUjjain9890912432638000

All the records present in the agents table are displayed in the ascending order of the customer's profit percentage.