ORACLE SQL ORDER BY training institutes in Ameerpet Hyderabad

SQL ORDER BY Clause

  • 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 will help us to sort the records based on the specific column of a table. This means that all the values stored in the column on which we are applying ORDER BY clause will be sorted, and the corresponding column values will be displayed in the sequence in which we have obtained the values 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 ORDER by clause. 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, 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:

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

Syntax to sort the records in descending order:

  1. SELECT ColumnName1,...,ColumnNameN FROM TableName  ORDER BY ColumnNameDESC;

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

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

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
1 Himani Gupta 21 Modinagar 22000
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.

Current Time 1:04
Duration 18:10

Loaded: 11.74%

Advertisement

Query:

  1. 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.

You will get the following output:

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

Query:

  1. mysql> SELECT *FROM customers ORDER BY Address;

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:

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

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 descending order of the customer salary stored in the customers table.

Query:

  1. mysql> SELECT *FROM customers ORDER BY Salary DESC;

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

You will get the following output:

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

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

Example 4:

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

Query:

  1. 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.

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

AID Name WorkArea Profit_Percent ContactNumber Salary
1 Gurpreet Singh Bangalore 1 9989675432 43000
2 Sakshi Kumari Chennai 5 8190567342 25000
3 Prachi Desai Mumbai 2 9056123432 60000
4 Shivani More Pune 3 8894236789 35500
5 Pallavi Singh Delhi 4 7798092341 38700
6 Rohini Kulkarni Ambala 8 7890945612 25670
7 Shweta Dixit Chandigarh 6 8898786453 31670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
9 Anushka Tripathi Ujjain 9 8909124326 38000
10 Devika Sharma Goa 7 7864523145 44050

Example 1:

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

Query:

  1. mysql> SELECT *FROM agents 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.

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
9 Anushka Tripathi Ujjain 9 8909124326 38000
10 Devika Sharma Goa 7 7864523145 44050
1 Gurpreet Singh Bangalore 1 9989675432 43000
5 Pallavi Singh Delhi 4 7798092341 38700
3 Prachi Desai Mumbai 2 9056123432 60000
6 Rohini Kulkarni Ambala 8 7890945612 25670
2 Sakshi Kumari Chennai 5 8190567342 25000
4 Shivani More Pune 3 8894236789 35500
7 Shweta Dixit Chandigarh 6 8898786453 31670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050

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 descending order of the work area stored in the agents table.

Query:

  1. mysql> SELECT *FROM agents ORDER BY WorkArea DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
9 Anushka Tripathi Ujjain 9 8909124326 38000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
4 Shivani More Pune 3 8894236789 35500
3 Prachi Desai Mumbai 2 9056123432 60000
10 Devika Sharma Goa 7 7864523145 44050
5 Pallavi Singh Delhi 4 7798092341 38700
2 Sakshi Kumari Chennai 5 8190567342 25000
7 Shweta Dixit Chandigarh 6 8898786453 31670
1 Gurpreet Singh Bangalore 1 9989675432 43000
6 Rohini Kulkarni Ambala 8 7890945612 25670

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

Example 3:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY Salary;

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:

AID Name WorkArea Profit_Percent ContactNumber Salary
2 Sakshi Kumari Chennai 5 8190567342 25000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
6 Rohini Kulkarni Ambala 8 7890945612 25670
7 Shweta Dixit Chandigarh 6 8898786453 31670
4 Shivani More Pune 3 8894236789 35500
9 Anushka Tripathi Ujjain 9 8909124326 38000
5 Pallavi Singh Delhi 4 7798092341 38700
1 Gurpreet Singh Bangalore 1 9989675432 43000
10 Devika Sharma Goa 7 7864523145 44050
3 Prachi Desai Mumbai 2 9056123432 60000

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

Example 4:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY Salary DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
3 Prachi Desai Mumbai 2 9056123432 60000
10 Devika Sharma Goa 7 7864523145 44050
1 Gurpreet Singh Bangalore 1 9989675432 43000
5 Pallavi Singh Delhi 4 7798092341 38700
9 Anushka Tripathi Ujjain 9 8909124326 38000
4 Shivani More Pune 3 8894236789 35500
7 Shweta Dixit Chandigarh 6 8898786453 31670
6 Rohini Kulkarni Ambala 8 7890945612 25670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
2 Sakshi Kumari Chennai 5 8190567342 25000

All the records present in the agents table are displayed in the descending order of the customer's address.

 

 

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:

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

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

  1. SELECT ColumnName1,…,ColumnNameN FROM TableName  ORDER BY ColumnName;

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
1 Himani Gupta 21 Modinagar 22000
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:

  1. 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.

You will get the following output:

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

Query:

  1. mysql> SELECT *FROM customers ORDER BY Address;

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:

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

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:

  1. mysql> SELECT *FROM customers ORDER BY Salary ASC;

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:

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

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:

  1. mysql> SELECT *FROM customers ORDER BY Age;

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:

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

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:

AID Name WorkArea Profit_Percent ContactNumber Salary
1 Gurpreet Singh Bangalore 1 9989675432 43000
2 Sakshi Kumari Chennai 5 8190567342 25000
3 Prachi Desai Mumbai 2 9056123432 60000
4 Shivani More Pune 3 8894236789 35500
5 Pallavi Singh Delhi 4 7798092341 38700
6 Rohini Kulkarni Ambala 8 7890945612 25670
7 Shweta Dixit Chandigarh 6 8898786453 31670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
9 Anushka Tripathi Ujjain 9 8909124326 38000
10 Devika Sharma Goa 7 7864523145 44050

Example 1:

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

Query:

  1. mysql> SELECT *FROM agents 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.

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
9 Anushka Tripathi Ujjain 9 8909124326 38000
10 Devika Sharma Goa 7 7864523145 44050
1 Gurpreet Singh Bangalore 1 9989675432 43000
5 Pallavi Singh Delhi 4 7798092341 38700
3 Prachi Desai Mumbai 2 9056123432 60000
6 Rohini Kulkarni Ambala 8 7890945612 25670
2 Sakshi Kumari Chennai 5 8190567342 25000
4 Shivani More Pune 3 8894236789 35500
7 Shweta Dixit Chandigarh 6 8898786453 31670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050

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:

  1. mysql> SELECT *FROM agents ORDER BY Salary;

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:

AID Name WorkArea Profit_Percent ContactNumber Salary
2 Sakshi Kumari Chennai 5 8190567342 25000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
6 Rohini Kulkarni Ambala 8 7890945612 25670
7 Shweta Dixit Chandigarh 6 8898786453 31670
4 Shivani More Pune 3 8894236789 35500
9 Anushka Tripathi Ujjain 9 8909124326 38000
5 Pallavi Singh Delhi 4 7798092341 38700
1 Gurpreet Singh Bangalore 1 9989675432 43000
10 Devika Sharma Goa 7 7864523145 44050
3 Prachi Desai Mumbai 2 9056123432 60000

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:

  1. mysql> SELECT *FROM agents ORDER BY WorkArea;

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:

AID Name WorkArea Profit_Percent ContactNumber Salary
6 Rohini Kulkarni Ambala 8 7890945612 25670
1 Gurpreet Singh Bangalore 1 9989675432 43000
7 Shweta Dixit Chandigarh 6 8898786453 31670
2 Sakshi Kumari Chennai 5 8190567342 25000
5 Pallavi Singh Delhi 4 7798092341 38700
10 Devika Sharma Goa 7 7864523145 44050
3 Prachi Desai Mumbai 2 9056123432 60000
4 Shivani More Pune 3 8894236789 35500
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
9 Anushka Tripathi Ujjain 9 8909124326 38000

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:

  1. mysql> SELECT *FROM agents ORDER BY Profit_Percent ASC;

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:

AID Name WorkArea Profit_Percent ContactNumber Salary
1 Gurpreet Singh Bangalore 1 9989675432 43000
3 Prachi Desai Mumbai 2 9056123432 60000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
4 Shivani More Pune 3 8894236789 35500
5 Pallavi Singh Delhi 4 7798092341 38700
2 Sakshi Kumari Chennai 5 8190567342 25000
7 Shweta Dixit Chandigarh 6 8898786453 31670
10 Devika Sharma Goa 7 7864523145 44050
6 Rohini Kulkarni Ambala 8 7890945612 25670
9 Anushka Tripathi Ujjain 9 8909124326 38000

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

 

SQL ORDER BY CLAUSE WITH DESCENDING 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 to sort the records based on the specific column of a table. 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. 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 descending order:

  1. SELECT ColumnName1,…,ColumnNameN FROM TableName  ORDER BY ColumnNameDESC;

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
1 Himani Gupta 21 Modinagar 22000
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 descending order of the customer names stored in the customers table.

Query:

  1. mysql> SELECT *FROM customers ORDER BY Name DESC;

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

You will get the following output:

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

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

Example 2:

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

Query:

  1. mysql> SELECT *FROM customers ORDER BY Address DESC;

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

You will get the following output:

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

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

Example 3:

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

Query:

  1. mysql> SELECT *FROM customers ORDER BY Salary DESC;

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

You will get the following output:

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

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

Example 4:

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

Query:

  1. 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.

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

AID Name WorkArea Profit_Percent ContactNumber Salary
1 Gurpreet Singh Bangalore 1 9989675432 43000
2 Sakshi Kumari Chennai 5 8190567342 25000
3 Prachi Desai Mumbai 2 9056123432 60000
4 Shivani More Pune 3 8894236789 35500
5 Pallavi Singh Delhi 4 7798092341 38700
6 Rohini Kulkarni Ambala 8 7890945612 25670
7 Shweta Dixit Chandigarh 6 8898786453 31670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
9 Anushka Tripathi Ujjain 9 8909124326 38000
10 Devika Sharma Goa 7 7864523145 44050

Example 1:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY Name DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
7 Shweta Dixit Chandigarh 6 8898786453 31670
4 Shivani More Pune 3 8894236789 35500
2 Sakshi Kumari Chennai 5 8190567342 25000
6 Rohini Kulkarni Ambala 8 7890945612 25670
3 Prachi Desai Mumbai 2 9056123432 60000
5 Pallavi Singh Delhi 4 7798092341 38700
1 Gurpreet Singh Bangalore 1 9989675432 43000
10 Devika Sharma Goa 7 7864523145 44050
9 Anushka Tripathi Ujjain 9 8909124326 38000

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

Example 2:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY Salary DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
3 Prachi Desai Mumbai 2 9056123432 60000
10 Devika Sharma Goa 7 7864523145 44050
1 Gurpreet Singh Bangalore 1 9989675432 43000
5 Pallavi Singh Delhi 4 7798092341 38700
9 Anushka Tripathi Ujjain 9 8909124326 38000
4 Shivani More Pune 3 8894236789 35500
7 Shweta Dixit Chandigarh 6 8898786453 31670
6 Rohini Kulkarni Ambala 8 7890945612 25670
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
2 Sakshi Kumari Chennai 5 8190567342 25000

All the records present in the agents table are displayed in the descending order of the agent's salary.

Example 3:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY WorkArea DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
9 Anushka Tripathi Ujjain 9 8909124326 38000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
4 Shivani More Pune 3 8894236789 35500
3 Prachi Desai Mumbai 2 9056123432 60000
10 Devika Sharma Goa 7 7864523145 44050
5 Pallavi Singh Delhi 4 7798092341 38700
2 Sakshi Kumari Chennai 5 8190567342 25000
7 Shweta Dixit Chandigarh 6 8898786453 31670
1 Gurpreet Singh Bangalore 1 9989675432 43000
6 Rohini Kulkarni Ambala 8 7890945612 25670

All the records present in the agents table are displayed in the descending order of the agent's workarea.

Example 4:

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

Query:

  1. mysql> SELECT *FROM agents ORDER BY Profit_Percent DESC;

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

You will get the following output:

AID Name WorkArea Profit_Percent ContactNumber Salary
9 Anushka Tripathi Ujjain 9 8909124326 38000
6 Rohini Kulkarni Ambala 8 7890945612 25670
10 Devika Sharma Goa 7 7864523145 44050
7 Shweta Dixit Chandigarh 6 8898786453 31670
2 Sakshi Kumari Chennai 5 8190567342 25000
5 Pallavi Singh Delhi 4 7798092341 38700
4 Shivani More Pune 3 8894236789 35500
3 Prachi Desai Mumbai 2 9056123432 60000
8 Sonakshi Tiwari Udaipur 2 9809453421 25050
1 Gurpreet Singh Bangalore 1 9989675432 43000

All the records present in the agents table are displayed in the descending order of the agent's profit percent.

 

SQL ORDER BY RANDOM

If you want the resulting record to be ordered randomly, you should use the following codes according to several databases.

Here is a question: what is the need to fetch a random record or a row from a database?

Sometimes you may want to display random information like articles, links, pages, etc., to your user.

If you want to fetch random rows from any of the databases, you have to use some altered queries according to the databases.

  • Select a random row with MySQL:

If you want to return a random row with MY SQL, use the following syntax:

  1. SELECT column FROM table ORDER BY RAND () LIMIT 1;
  • Select a random row with Postgre SQL:
  1. SELECT column FROM table ORDER BY RANDOM () LIMIT 1;
  • Select a random row with SQL Server:
  1. SELECT TOP 1 column FROM table ORDER BY NEWID ();
  • Select a random row with oracle:
  1. SELECT column FROM (SELECT column FROM table ORDER BY dbms_random.value) WHERE rownum = 1;
  • Select a random row with IBM DB2:
  1. SELECT column RAND () as IDX  FROM table ORDER BY  IDX FETCH FIRST 1 ROWS ONLY;

To understand this concept practically, let us see some examples using the MySQL database. Consider we have a table items created into the database with the following data:

Table: items

ID Item_Name Item_Quantity Item_Price Purchase_Date
1 Soap 5 200 2021-07-08
2 Toothpaste 2 80 2021-07-10
3 Pen 10 50 2021-07-12
4 Bottle 1 250 2021-07-13
5 Brush 3 90 2021-07-15

Suppose we want to retrieve any random record from the items table.

We will write the query as follows:

  1. mysql> SELECT * FROM items ORDER BY RAND () LIMIT 1;

We may get the following results:

ID Item_Name Item_Quantity Item_Price Purchase_Date
3 Pen 10 20 2021-07-12

Now let us try executing the same query one more time.

  1. mysql> SELECT * FROM items ORDER BY RAND () LIMIT 1;

We may get the following results:

ID Item_Name Item_Quantity Item_Price Purchase_Date
5 Brush 3 90 2021-07-15

From the above results, we can conclude that we get different records as output both times even though we executed the same query twice. RAND () function has selected random records both times for the same query from a single table. Therefore, even we execute the same query again, we will get different output every time. There is a rare possibility of getting the same record consecutively using the RAND () function.

Now, suppose you want all the records of the table to be fetched randomly.

To do so, we need to execute the following query:

  1. mysql> SELECT * FROM items ORDER BY RAND ();

We may get the following results:

ID Item_Name Item_Quantity Item_Price Purchase_Date
4 Bottle 1 250 2021-07-13
5 Brush 3 90 2021-07-15
1 Soap 5 200 2021-07-08
2 Toothpaste 2 80 2021-07-10
3 Pen 10 50 2021-07-12

There is also a possibility of getting some different arrangements of records if we execute the RAND () function again on the employees table.

 

SQL ORDER BY LIMIT

We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.

LIMIT CLAUSE FOR ORACLE SQL:

If you want to use LIMIT clause with SQL, you have to use ROWNUM queries because it is used after result are selected.

You should use the following code:

  1. SELECT name, age
  2. FROM
  3. (SELECT name, age, ROWNUM r
  4. FROM
  5. (SELECT name, age, FROM employee_data
  6. ORDER BY age DESC
  7. )
  8. WHERE ROWNUM <=40
  9. )
  10. WHERE r >= 21;

This query will give you 21th to 40th rows.

SQL SORTING ON MULTIPLE COLUMNS

Let's take an example of customer table which has many columns, the following SQL statement selects all customers from the table named "customer", stored by the "country" and "Customer-Name" columns:

  1. SELECT * FROM customers
  2. ORDER BY country, Customer-Name;