SQL Server ORDER BY

Learning Objective

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.

Operation

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.

table employees

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.

sql server order by one column

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;

Output

sql server order by one column in descending order

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.

sql server order by one column

Step 2 – Now resultset will be sorted by emp_location in descending order.

sql server order by multiple column

The above resultset will be the find output of the above query.

Advertisement