SQL Server – Sort data using ORDER BY

In this SQL Server tutorial, you’ll learn how to use the ORDER BY clause in your SQL queries along with the syntax and examples of its usage.

When you use the SELECT statement on a table to return the results, the order of the results is generally not guaranteed. You can ensure that the result set that is returned is sorted by using the ORDER BY clause.

Syntax

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

Parameters

• expressions
  • The columns that you wish to return when the SELECT query is run
• table
  • The table from where you want to get the records from. When you use FROM , you will need at least one table listed in the FROM.
• WHERE
  • This is optional and specifies the conditions needed to filter the records.
• ORDER BY
  • This is the keyword that specifies that the result set needs to be ordered by the specified expression
    • ASC – This is optional. It sorts the result set in ascending order by the specified expression. If ASC is not specified with the expression, it defaults to ascending order internally.
    • DESC – This is optional. It sorts the result set in descending by the specified expression.

1. Sort result by a column in ascending order

By default, the ORDER BY clause will sort the result set in ascending order when you don’t specify ASC or DESC.

Let’s see an example

SELECT *
FROM [Person].[Person] 
ORDER BY FirstName

The above SQL query will return all the records from the Person table sorted by FirstName in ascending order. Since we have not specified ASC/DESC, it is the same as the below query

SELECT * 
FROM [Person].[Person] 
ORDER BY FirstName ASC

2. Sort result by a column in descending order

To sort the result set in descending order, you will need to use the DESC attribute in your ORDER BY statement.

SELECT * FROM 
[Person].[Person]
ORDER BY FirstName DESC

The above SQL query will return all the records from the Person table sorted by FirstName in descending order.

3. Sort result by a multiple columns

The following SQL query gets all the records from the Person Table. It sorts the result first by the FirstName and then by the LastName.

SELECT *
FROM [Person].[Person] 
ORDER BY FirstName, LastName

4. Sort result by using both ASC and DESC on multiple columns

When you sort your result set using the ORDER BY clause, you can specify the ASC and DESC for different columns.

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

The above query would return all the records from the table “Person” sorted by FirstName in ascending order and then for all the records with the same FirstName, it then sorts by LastName in ascendingorder.

5. Sort result by relative position

If you don’t want to use the column name but instead want to sort the records by the relative position in the result set, you can use the numeric values to represent it.

For example,

SELECT FirstName
FROM [Person].[Person] 
ORDER BY 1

The above SQL query sorts the results by FirstName as 1 is the first field in the result set and is the firstname.

Note : If the index/relative position does not exist in the result set, you will receive the below error

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 2 is out of range of the number of items in the select list.

It is a good practice to avoid using the relative position and specify the column names explicitly in the ORDER BY clause in SQL Server.

6. Sort result by expression

You can use expressions to sort the results when using the ORDER BY clause in SQL Server. For example, LEN function is a example that returns the number of characters in a string. We can use it to sort the result set as shown below.

SELECT FirstName
FROM [Person].[Person] 
ORDER BY LEN(FirstName)