When we execute the data using SQL SELECT statement then data is sorted as default order in search result. It means data in the search result is displayed as added in the database table. In this article we are concentrating on simple ways to sort the data differently using Order by clause & how we sort the data on multiple columns using single query. Also we are covering how to sort the data for different data types like String, Datetime, Numeric etc.
Order by clause:
To sort the result set we can use Order by clause in either ascending or descending order. This clause is used with conjection of SELECT statement.
What are DESC and ASC Keywords?
| DESC is used to the query to sort result by descending | ASC is used to the query to sort result by ascending |
| Query result is sorted from Bottom to Top. | Query result is sorted from Top to Bottom. |
| For Date types the latest dates are resulted at the top of the result list. | For Date types the old dates are resulted at the top of the result list. |
| For Numeric data types the Largest number are resulted at the top of the result list. | For Numeric data types the lowest number are resulted at the top of the result list. |
| For String data types the result list is sorted with the letter from Z to A. | For String data types the result list is sorted with the letter from A to Z. |
The DESC and ASC keywords are used with the combination of Select statement & the ORDER BY clause.
Let’s see how these both keywords are use in the basic query statement:
ASC/DESC basic syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name(s) ASC|DESCSo here we see what is use of each objects:
- [SELECT column_name(s)] – The result set retrieve all entered columns data. It may contain multiple columns. If pass ‘*’ then it results all columns from selected table.
- [FROM table_name] – This is a table name from which result set is retrieved.
- [WHERE condition] – It is used to restrict result set based on the given condition. It is optional Condition.
- ORDER BY –The ORDER BY keyword is used to sort the result-set by a specified column. If we do not pass the ASC or DESC then default ASC is considered.
Examples:
Let’s see upon executing following query on Employee table resulting following list:
SELECT * FROM EmployeeFollowing is the result upon executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
| 5 | Peter Willams | Male | 1980-07-12 | 9944552299 |
If company wants the list of employee details list with the elder to younger as Birthday. So you can get a list by executing query on database using Order by keyword with DESC as below.
SELECT * FROM Employee
ORDER BY Date_Of_Birth DESCFollowing is the result upon executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
| 5 | Peter Willams | Male | 1980-07-12 | 9944552299 |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
Note: In the sorting NULL values means consider as no values i.e. not zero or empty string. In this example we also see how the NULL results are sorted.
We can use same query with ascending order by using following query:
SELECT * FROM Employee
ORDER BY Date_Of_Birth ASCor
SELECT * FROM Employee
ORDER BY Date_Of_BirthFollowing is the result upon executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 5 | Peter Willams | Male | 1980-07-12 | 9944552299 |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
If we sort the result using String then, the query result set are sorted from those starting with the letter Z going down to the letter A.
SELECT * FROM Employee
ORDER BY Full_Name ASCFollowing is the result upon executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
| 5 | Peter Willams | Male | 1980-07-12 | 9944552299 |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
We can use the Sorting of two column using Single query. In this example we are sorting first column with ascending & second column with descending order.
SELECT * FROM Employee
ORDER BY Gender, Date_Of_Birth DESCFollowing is the result upon executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
| 5 | Peter Willams | Male | 1980-07-12 | 9944552299 |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
The gender column was sorted in ascending order by default while the Birth date column was sorted in descending order explicitly.
ORDER BY, DESC and ASC
- You can sort the result ser either in ascending or descending order using ASC or DESC keywords.
- To sort the result in ascending order the “ASC” keyword is used.
- If we do not pass the keyword with the ORDER BY then default keyword “ASC” is used to sort the search result.
- To sort the result in descending order the “DESC” keyword is used.
- Using Order by clause conjunction with SELECT both DESC and ASC works. To limit the search result we can use WHERE clause.
If you enjoy reading this ORDER BY, DESC and ASC – Database Testing article please make sure to share it with your friends. Please leave your questions/tips/suggestions in the comment section below and I’ll try to answer as many as I can.
⇓ Subscribe Us ⇓
If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:
Happy Testing!!!