SQL ORDER BY Clause

by Taiba Hamid · Published July 30, 2015 · Updated September 24, 2015

ORDER BY clause is used to sort the data in ascending or descending order, in SQL Server by default data will not be inserted in any order even if you have an index.

Syntax:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

We will take an example of table [Person] to sort the column order by FirstName in ascending order.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName;

If you do not specify the keyword ASC, sorting by default will be in ascending order, this is equivalent of below query.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC;

If you want to order the data in descending order then you have to specify ORDER BY DESC as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName DESC;

Sorting data using the numerical position of a column name

Instead of column name you can also specify numeric position of column to sort data as shown below.

SELECT
      [FirstName]
     ,[MiddleName]
     ,[LastName]
FROM [Person].[Person]
ORDER BY 1 ASC;

Note

It is considered bad practice to use numerical position instead of column names. For example, if you change the positions of columns, or if you add columns then it will give error.

Using more than one column to sort data
Here we are sorting two columns FirstName and LastName.

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName, LastName;

Combining Ascending and Descending order

With order by clause we can combine the ASC and DESC keywords in a single statement, here is an example:

SELECT
       [FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]
ORDER BY FirstName ASC, LastName DESC;

Sorting Data using Column Alias

The use of an alias is allowed because the order by clause is last in logical query processing order, here is an example. [Refer SELECT Statement]

SELECT
      [FirstName]     
     ,[MiddleName]
     ,[LastName]
     ,[FirstName]+' '+[LastName] AS [FullName]
FROM [Person].[Person]
ORDER BY [FullName];