SQL Order by Clause – ASC/DESC keywords

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|DESC

So 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 Employee

Following 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 DESC

Following 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 ASC

or

SELECT * FROM Employee
ORDER BY Date_Of_Birth

Following 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 ASC

Following 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 DESC

Following 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:

Enter your email address:

 

Check email in your inbox for confirmation to get latest updates Software Testing for free.

  Happy Testing!!!