- ORDER BY is a clause in SQL which is used with SELECT query to fetch the records in ascending or descending order from a table.
- Just like we sort the integer and the string values stored in the column of the tables, similarly, we can sort the dates stored in the SQL table’s column.
- All the records will be, by default, sorted in the ascending order. To sort the records in descending order, the DESC keyword is used.
Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.
Consider we have created a table named as employees in MySQL database with the following data:
| ID | Name | Salary | Joining_Date | DOB |
|---|---|---|---|---|
| 1 | Rohit More | 50000 | 2020-02-08 | 1991-01-28 18:06:08 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990-05-15 19:10:00 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983-02-20 12:18:45 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978-06-29 15:45:13 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999-03-21 02:14:56 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998-08-02 13:00:01 |
Example 1:
Write a query to display all the details of employees arranged in ascending order of their date of birth.
Query:
mysql> SELECT *FROM employees ORDER BY DOB; Since we wanted to sort the records in the ascending order of the date of birth of employees, so we have applied the ORDER BY clause on the column ‘DOB’.
You will get the following output:
| ID | Name | Salary | Joining_Date | DOB |
|---|---|---|---|---|
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978-06-29 15:45:13 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983-02-20 12:18:45 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990-05-15 19:10:00 |
| 1 | Rohit More | 50000 | 2020-02-08 | 1991-01-28 18:06:08 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998-08-02 13:00:01 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999-03-21 02:14:56 |
The results obtained from the above query show that the records are displayed according to the ascending order of the DOB.
Example 2:
Write a query to display all the details of employees arranged in descending order of their joining dates.
Query:
mysql> SELECT *FROM employees ORDER BY Joining_Date DESC; Since we wanted to sort the records in the descending order of the joining date of employees, so we have applied the ORDER BY clause with the DESC keyword on the column ‘Joining_Date’.
You will get the following output:
| ID | Name | Salary | Joining_Date | DOB |
|---|---|---|---|---|
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998-08-02 13:00:01 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990-05-15 19:10:00 |
| 1 | Rohit More | 50000 | 2020-02-08 | 1991-01-28 18:06:08 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978-06-29 15:45:13 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983-02-20 12:18:45 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999-03-21 02:14:56 |
The results obtained from the above query show that the records are displayed according to the descending order of the joining dates.
Example 3:
Write a query to display all the details of employees arranged in ascending order of their year of birth.
Query:
mysql> SELECT ID, Name, Salary, Joining_Date, DATE_FORMAT (DOB, '%Y') AS Year_of_Birth FROM employees ORDER BY DATE_FORMAT (DOB, '%Y'); Since we wanted to sort the records in the ascending order of the birth year of employees, so we have applied the ORDER BY clause. DATE_FORMAT () function is applied on the column DOB with the parameter ‘%Y’ to extract only the year from ‘DOB’.
You will get the following output:
| ID | Name | Salary | Joining_Date | Year_Of_Birth |
|---|---|---|---|---|
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990 |
| 1 | Rohit More | 50000 | 2020-02-08 | 1991 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999 |
The results obtained from the above query show that the records are displayed according to the ascending order of the employee’s year of birth.
Example 4:
Write a query to display all the details of employees arranged in descending order of their hour of birth.
Query:
mysql> SELECT ID, Name, Salary, Joining_Date, DATE_FORMAT (DOB, '%H') AS Hour_Of_Birth FROM employees ORDER BY DATE_FORMAT (DOB, '%H') DESC; Since we wanted to sort the records in the descending order of the birth hour of employees, so we have applied the ORDER BY clause with the DESC keyword. DATE_FORMAT () function is applied on the column DOB with the parameter ‘%H’ to extract only the hour of birth from the column’DOB’.
You will get the following output:
| ID | Name | Salary | Joining_Date | Hour_Of_Birth |
|---|---|---|---|---|
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 19 |
| 1 | Rohit More | 50000 | 2020-02-08 | 18 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 15 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 13 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 12 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 02 |
The results obtained from the above query show that the records are displayed according to the descending order of the employee’s hour of birth.
Example 5:
Write a query to display all the details of employees arranged in ascending order of their year of joining.
Query:
mysql> SELECT ID, Name, Salary, DOB, DATE_FORMAT (Joining_Date, '%Y') AS Year_Of_Joining FROM employees ORDER BY DATE_FORMAT (Joining_Date, '%Y'); Since we wanted to sort the records in the ascending order of the joining year of employees, so we have applied the ORDER BY clause. DATE_FORMAT () function is applied on the column Joining_Date with the parameter ‘%Y’ to extract only the year from ‘Joining_Date’.
You will get the following output:
| ID | Name | Salary | Year_Of_Joining | DOB |
|---|---|---|---|---|
| 5 | Krishna Sharma | 48000 | 2010 | 1999-03-21 02:14:56 |
| 3 | Saurabh Jha | 61000 | 2015 | 1983-02-20 12:18:45 |
| 4 | Anant Desai | 59000 | 2018 | 1978-06-29 15:45:13 |
| 1 | Rohit More | 50000 | 2020 | 1991-01-28 18:06:08 |
| 2 | Kunal Mohite | 34000 | 2021 | 1990-05-15 19:10:00 |
| 6 | Bhavesh Jain | 37000 | 2021 | 1998-08-02 13:00:01 |
The results obtained from the above query show that the records are displayed according to the ascending order of the joining year of employees.
Example 6:
Write a query to display all the details of employees arranged in descending order of the joining day of employees.
Query:
mysql> SELECT ID, Name, Salary, DAY (Joining_Date) AS Day_Of_Joining, DOB FROM employees ORDER BY DAY (Joining_Date) DESC; Since we wanted to sort the records in the descending order of the joining day of employees, so we have applied the ORDER BY clause with the DESC keyword. DAY () function is applied on the column ‘Joining_Date’ to extract only the day of joining from Joining_Date.
You will get the following output:
| ID | Name | Salary | Day_Of_Joining | DOB |
|---|---|---|---|---|
| 4 | Anant Desai | 59000 | 27 | 1978-06-29 15:45:13 |
| 5 | Krishna Sharma | 48000 | 23 | 1999-03-21 02:14:56 |
| 1 | Rohit More | 50000 | 8 | 1991-01-28 18:06:08 |
| 6 | Bhavesh Jain | 37000 | 3 | 1998-08-02 13:00:01 |
| 2 | Kunal Mohite | 34000 | 1 | 1990-05-15 19:10:00 |
| 3 | Saurabh Jha | 61000 | 1 | 1983-02-20 12:18:45 |
The results obtained from the above query show that the records are displayed according to the descending order of the joining day of employees.
Example 7:
Write a query to display all the details of employees arranged in ascending order of the birth day of employees.
Query:
mysql> SELECT ID, Name, Salary, Joining_Date, DAY (DOB) AS Day_Of_Birth FROM employees ORDER BY DAY (DOB); Since we wanted to sort the records in the ascending order of the day of birth of employees, so we have applied the ORDER BY clause. DAY () function is applied on the column ‘DOB’ to extract only the day of birth from DOB.
You will get the following output:
| ID | Name | Salary | Year_Of_Joining | Day_Of_Birth |
|---|---|---|---|---|
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 2 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 15 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 20 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 21 |
| 1 | Rohit More | 50000 | 2020-02-08 | 28 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 29 |
The results obtained from the above query show that the records are displayed according to the ascending order of the employee’s day of birth.
Example 8:
Write a query to display all the details of employees arranged in ascending order of the employee’s birth month.
Query:
mysql> SELECT ID, Name, Salary, Joining_Date, DOB FROM employees ORDER BY MONTH (DOB); | ID | Name | Salary | Joining_Date | DOB |
|---|---|---|---|---|
| 1 | Rohit More | 50000 | 2020-02-08 | 1991-01-28 18:06:08 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983-02-20 12:18:45 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999-03-21 02:14:56 |
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990-05-15 19:10:00 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978-06-29 15:45:13 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998-08-02 13:00:01 |
The results obtained from the above query show that the records are displayed according to the ascending order of the employee’s birth month.
Example 9:
Write a query to display all the details of employees arranged in ascending order of the employee’s month of joining.
Query:
mysql> SELECT ID, Name, Salary, Joining_Date, DOB FROM employees ORDER BY MONTH (Joining_Date); Since we wanted to sort the records in the ascending order of the joining month of employees, so we have applied the ORDER BY clause. MONTH () function is applied on the column ‘Joining_Date’ to extract only the joining month from Joining_Date.
You will get the following output:
| ID | Name | Salary | Joining_Date | DOB |
|---|---|---|---|---|
| 2 | Kunal Mohite | 34000 | 2021-01-01 | 1990-05-15 19:10:00 |
| 1 | Rohit More | 50000 | 2020-02-08 | 1991-01-28 18:06:08 |
| 3 | Saurabh Jha | 61000 | 2015-05-01 | 1983-02-20 12:18:45 |
| 6 | Bhavesh Jain | 37000 | 2021-07-03 | 1998-08-02 13:00:01 |
| 4 | Anant Desai | 59000 | 2018-08-27 | 1978-06-29 15:45:13 |
| 5 | Krishna Sharma | 48000 | 2010-10-23 | 1999-03-21 02:14:56 |
The results obtained from the above query show that the records are displayed according to the ascending order of the employee’s month of joining.
Leave a Reply