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.
|
1 |
SELECT * FROM table_name ORDER BY [column_name] ASC|DESC |
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.
|
1 2 3 4 5 6 7 8 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Order by BirthDate |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Order by BirthDate DESC |
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.
|
1 2 3 4 5 6 7 8 |
SELECT [NationalIDNumber] ,[JobTitle] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Order by BirthDate DESC |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Order by 3 DESC |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [NationalIDNumber] ,SickLeaveHours ,[Vacationhours] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] where MaritalStatus='M' Order by SickLeaveHours ASC , [Vacationhours] desc |
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.
|
1 2 3 |
SELECT Max(SickLeaveHours) FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' |
In the following screenshot, you can see we get the output without any column name.
Let’s rerun query using an alias.
|
1 2 3 |
SELECT Max(SickLeaveHours) as MAXSickHours FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [NationalIDNumber] , SickLeaveHours as [SickHours] --Alias Column ,[Vacationhours] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' Order by [SickHours] DESC --Sort by Alias |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT top 10 [NationalIDNumber] , SickLeaveHours as [SickHours] ,[Vacationhours] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT top 10 [NationalIDNumber] , SickLeaveHours as [SickHours] ,[Vacationhours] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' Order by DATEPART(YEAR , BirthDate) ASC |
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.
Latin1_General_CI_AI |
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive |
Latin1_General_CS_AS |
Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive |
|
1 2 3 4 5 |
Use SQLShackDemo Go CREATE TABLE #temp1 (EmpName nvarchar(20) COLLATE Latin1_General_CI_AI) GO INSERT INTO #temp1 VALUES(N'Rajendra'),(N'raJendra'),(N'rajendraA'),(N'rAjEnDrA'),(N'rajendra'),(N'RAJENDRA'); |
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:
|
1 2 3 |
SELECT EmpName FROM #temp1 ORDER BY EmpName; |
Query 2:
|
1 2 3 |
SELECT EmpName FROM #temp1 ORDER BY EmpName COLLATE Latin1_General_CS_AS |
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.
- ROW_NUMBER
- RANK
- DENSE_RANK
- 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.
|
1 2 3 4 5 |
SELECT [NationalIDNumber] ,SickLeaveHours ,ROW_NUMBER() OVER (ORDER BY [SickLeaveHours] ASC ) AS "Row_Number" FROM [AdventureWorks2017].[HumanResources].[Employee] Where MaritalStatus='M' |
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.
|
1 2 3 4 5 6 |
SELECT [NationalIDNumber] , SickLeaveHours ,ROW_NUMBER() OVER (ORDER BY [SickLeaveHours] ASC ) AS "Row_Number" ,RANK() OVER (ORDER BY SickLeaveHours) AS "Rank" FROM [AdventureWorks2017].[HumanResources].[Employee] where MaritalStatus='M' |
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.
|
1 2 3 4 5 6 7 8 |
SELECT [NationalIDNumber] , SickLeaveHours ,ROW_NUMBER() OVER (ORDER BY [SickLeaveHours] ASC ) AS "Row Number" ,RANK() OVER (ORDER BY SickLeaveHours) AS "Rank" ,DENSE_RANK() OVER (ORDER BY SickLeaveHours) AS "DENSE_Rank", NTILE(4) OVER (ORDER BY SickLeaveHours) AS [NTILE] FROM [AdventureWorks2017].[HumanResources].[Employee] where MaritalStatus='M' |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] where Salariedflag=0 Order by BirthDate |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] where Salariedflag=0 Order by BirthDate OFFSET 100 ROWS; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [NationalIDNumber] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[ModifiedDate] ,[SalariedFlag] FROM [AdventureWorks2017].[HumanResources].[Employee] Where Salariedflag=0 Order by BirthDate OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY; |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023