The objective of this SQL Server tutorial is to teach you how to sort data in a result set retrieved through a select query using the ORDER BY clause in SQL Server.
What is ORDER BY clause in SQL Server?
The SQL Server ORDER BY clause as the name indicates is used to sort output alphabetically or numerically. It takes two arguments – ASC or DESC. ASC sorts the output in ascending order
(from a to z in case of alphabets and from lower to higher value in case of numbers). It is the
default mode and is applied automatically. DESC sorts the output in descending order and has to be specified explicitly.
The SQL Server ORDER BY clause can be used on a single column or on more than one column in a select query. When used on multiple columns it sorts the output first on the first specified column and then sorts the sorted output again on the next specified column and so on and the final output is the result of the sequential sorting.
SQL Server ORDER BY Syntax
The basic syntax of SQL Server ORDER BY clause is as follows.
SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]];
In this syntax,
- expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
- tables – one or more than one table from where you want to retrieve data.
- WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
- ORDER BY – This argument is used to sort the resultset. If you want to sort on more than one column, you need to provide them in comma-separated.
- ASC – Optional. ASC sort the resultset in ascending order. This is the default behavior if no modifier is mentioned.
- DESC – Optional. DESC sorts resultset in descending order by expression.
SQL Server ORDER BY Clause Examples
Let us see how it works starting from the simple (i.e. single column scenario) to the complex (i.e. multiple column scenario).
NOTE: Columns are also referred to as fields and the terms are used interchangeably.
Suppose we have an employee table with the following data.
1) SQL Server ORDER BY – Sort by one column in ascending order
The SQL Server
ORDER BY clause can be used to sort the result in ascending order. The following statement sort the employee list by dept_code in ascending order.
SELECT * FROM employee ORDER BY dept_code ASC;
The above query will generate the output as below. You can notice that the table is now sorted by dept_code in ascending order.
If you just omit the
ASC expression from the above statement, you will notice that the
ORDER BY clause without argument also sort in ascending order. So, the below statement will produce the same output as above.
SELECT * FROM employee ORDER BY dept_code;
2) SQL Server ORDER BY – Sort by one column in descending order
In order to sort the column in descending order you need to user
DESC clause along with the
ORDER BY as follow.
SELECT * FROM employee ORDER BY dept_code DESC;
In the above example, you can see that
dept_code column is sorted in descending order.
3) SQL Server ORDER BY – Sort column by relative position
It is also possible to use the relative position number with the
ORDER BY clause instead of specifying the column or field name. Here the first column is denoted by 1, 2nd column by 2 and so on.
SELECT * FROM employee ORDER BY 1 ASC, 5 DESC;
The above statement will sort the resultset by column emp_id first and next it will sort by column dept code. So the below statement is equivalent to the above query.
SELECT * FROM employee ORDER BY emp_id ASC, dept_code DESC;
4) SQL Server ORDER BY – sort by multiple columns and in different order
The following query sorts the employees first by the
emp_id in ascending order then the sorted resultset by
dept_code in descending order.
SELECT * FROM employee ORDER BY dept_code ASC, emp_location DESC;
Step 1 – First sort employees by
dept_code in ascending order.
Step 2 – Now resultset will be sorted by
emp_location in descending order.
The above resultset will be the find output of the above query.