SQL Order by Clause overview and examples (2024)

This article will cover the SQL ORDER BY clause including syntax, usage scenarios to sort out results in a Select statement.

Once we execute a Select statement in SQL Server, it returns unsorted results. We can define a sequence of a column in the select statement column list. We might need to sort out the result set based on a particular column value, condition etc. We can sort results in ascending or descending order with an ORDER BY clause in Select statement.

SQL Order By clause syntax

We can see the syntax for SQL Order by clause as follows.

In SQL ORDER BY clause, we need to define ascending or descending order in which result needs to be sorted.

• ASC: We can specify ASC to sort the result in ascending order
• DESC: We can specify DESC to sort the result in descending order

By default, SQL Server sorts out results using ORDER BY clause in ascending order. Specifying ASC in order by clause is optional.

Let us explore the SQL ORDER BY clause using examples.

In this article, I am using AdventureWorks2017 database for all examples. Execute the following query in SSMS. We are not using ORDER BY clause in this query.

Example 1: Sort results on a column defined in a Select statement using SQL Order By clause

Suppose our requirement is to sort the result by BirthDate column. This column is also specified in the column list of Select statement.

It uses a default sort method (ascending) because we have not specified any sort order in this query.

If we want to sort out results in descending order on birthdate column, we can specify DESC in order by clause.

Example 2: Sort results on a column not defined in a Select statement using SQL Order By clause

In the previous example, we sorted out results on the birthdate column. We have specified this column in select statement column list as well.

Suppose we want to sort out results on birthday column, but this column is not specified in the select statement. We can still sort results on a column not defined in a Select statement.

Example 3: Sort results by column positions in a Select statement using SQL Order By clause

In previous examples, we specified the column name in Order by clause to sort results in ascending or descending order. We can also specify column position in Order by clause.

In this query, column birthdate is at the 3rd position; therefore, we can use three in the Order by clause to sort results on this column data.

Note: I would not recommend using column position in Order By clause. You should always use a column name in Order by clause.

• Finding out sort column name might be inconvenient. If we are using a large query, it becomes difficult to identify each column position
• If we make any changes in the column list of Select statement, we need to change the value in order by clause to reflect correct column position no

Example 4: Sort results on multiple columns in a Select statement using SQL Order By clause

We can sort results based on multiple columns as well in a select statement. Suppose we want to get results in the following the order.

• SickLeaveHours in Ascending order
• Vacationhours in Descending order

We can specify both ascending and descending order on both columns as shown in the following query.

In the following screenshot, you can see the result is sorted in ascending order for SickLeaveHours. If there are multiple rows with the same value for SickLeaveHours, it further sorts results on Vacationhours in descending order.

Example 5: Sort results on alias columns in a Select statement using SQL Order By clause

Many times, we define an alias on a column in a Select statement. Suppose you want to get maximum value in a column using the max function. We can specify a column name to appear in the output. If we do not specify any column name, we get the output without any column name.

In the following screenshot, you can see we get the output without any column name.

Let’s rerun query using an alias.

We can use the Order by clause for an alias column as well. Suppose we want to define an alias on SickLeaveHours as [SickHours]. Further, we want to use this alias in Order by clause to sort results in descending order.

In the following screenshot, We can see alias column SickHours sorted in descending order.

Example 6: Sort results with expression in a Select statement using SQL Order By clause

We can use expressions as well in Order by clause. Suppose we want to sort for a year in a date column. Let us run the query without any Order by clause first.

Now, we want to sort on Birthdate column yearly in ascending order. I am using the TOP clause to limit result in the output. We are using DATEPART function we get a specified part of a date. In this query, we want to extract year to sort data using the DATEPART function.

Example 6: Sort results with Collation using SQL Order By clause

Suppose we have a table that contains column having case sensitive data. We might want to sort results specifying the collation in Order by clause.

Let us create a table with Latin1_General_CI_AI collation and later we will sort it using another collation Latin1_General_CS_AS.

You can find the definition of both collations in the following table.

Let us sort out results without specifying any sort condition. It uses the collation defined in the EmpName column (Latin1_General_CI_AI).

Query 1:

Query 2:

In the following output, you can see a difference in the result of both queries. In the Query1, it uses default column collation (Latin1_General_CI_AI) for sorting results. In Query2, it uses collation (Latin1_General_CS_AS) specified in Order by clause to sort results.

Example 7: Sort results using a Rank function using SQL Order By clause

We can use built-in Ranking functions in SQL Server with Order by clause as well. We can use Rank functions to provide a rank in a set of rows. We have following Rank functions in SQL Server.

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. NTILE

Let us explore the use of Order By clause with each Ranking functions.

ROW_NUMBER

We can use ROW_NUMBER to provide row number in a specified column based on Order By clause.

In the following query, we want to get row number for SickLeaveHours column values in ascending order.

RANK: In Rank function, we get a rank for each row based on column value specified. If there are multiple rows with a similar value, it gives the same rank but skips the next number in the sequence.

In the following query, we specified Row_Number() and RANK() function along with Order by clause for SickLeaveHours column.

In the following screenshot, we can see for result sorted out using SickLeaveHours. In the Rank function, it skips value 2-3 because we have 3 rows for SickLeaveHours 20.

Similarly, we can use DENSE_RANK() and NTILE(4) with Order by clause in a select statement.

In the output, we can see DENSE_RANK() gives a rank for each row based on the conditions specified in Order by clause. It does not skip the next value in rank if we have multiple rows with similar values.

The NTILE function divides the complete result sets into the number of groups specified.

Example 8: Limit number of rows using SQL Order By clause

We can skip some rows with OFFSET and FETCH in an Order by clause. First, let us run the following query and view the output in SSMS.

In the following screenshot, we can see this query returns 238 rows.

Suppose we want to Skip the first 100 rows from the sorted result set and return all remaining rows. We can use OFFSET with Order by clause.

In the output, we get 138 rows because it skips first 100 rows from the sorted result.

Suppose we want to skip 100 rows from the sorted result. We further need only first 20 rows from the result set. We can specify the following values along with Order by clause.

• OFFSET value of 100
• FETCH NEXT value 20

Execute the following query and view the output.

In the output, we can see that only the first 20 rows after skipping 100 records in sorted result.

Note: We can use OFFSET and FETCH in SQL Order By clause starting from SQL Server 2012.