Categories

Netsparker Web Application Security Scanner

SQL Order by clause

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.

Conclusion on 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.

Enter email to Subscribe:

1 comment to SQL Order by Clause – ASC/DESC keywords

  • vipin

    Thanks for such a effort, Very comprehensive

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>