A result set is easier to view when it’s ordered, because the data can be browsed in a meaningful way. SQL Server does not guarantee a result set is ordered unless an ORDER
BY
clause is used. Data can be sorted in ascending and/or descending order based on one or more columns when you use an ORDER
BY
clause. This articles explains different ways to use the ORDER
BY
clause for ordering a result set.
Syntax of the ORDER BY clause
Figure 1 contains the syntax for the ORDER
BY
clause as found in the Microsoft Documentation.
Figure 1: ORDER BY syntax
Test data
This article will cover a number of examples to show the different ways to use the ORDER
BY
clause. Each of these examples will select data from a table named TestData. If you want to follow along and run these examples, you can created this table by using the code in Listing 1.
Listing 1: Code to create TestData table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Create test data USE tempdb; GO CREATE TABLE TestData ( ID INT, CharID CHAR(2), CityName VARCHAR(20), StateName VARCHAR(20), Founded SMALLINT); --Insert rows of test data INSERT INTO TestData VALUES (1,'1','Seattle','Washington',1851), (11,'11','Redmond','washington',1871), (12,'12','Bellevue','Washington',1953), (2,'2','Portland','oregon',1851), (5,'5','Grants Pass','Oregon',1887), (6,'6','Spokane','washington',1881), (7,'7','Salem','oregon',1842), (8,'9','Bend','Oregon',1905), (9,'9','Tacoma','Washington',1872); |
Sorting based on a single column
If a SELECT
statement is run without and ORDER
BY
clause, SQL Server does not guarantee the record set produced in sorted order. To make sure a record set is returns data in sorted order an ORDER
BY
clause is needed. The code in Listing 2 does not have a ORDER
BY
clause, and produces the unordered results found in Report 1.
Listing 2: SELECT statement without an ORDER BY clause
1 |
SELECT StateName, CityName FROM TestData; |
Report 1: Unsorted Output when Listing 2 is run.
The ORDER
BY
clause will sort data based on one or more columns. The code in Listing 2 selects the StateName
, and CityName
columns from the TestData
table and sorts the results set based on the single column StateName
.
Listing 3: Sort based on single column
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName From TestData ORDER BY StateName; |
Report 2 shows the results when Listing 3 is executed.
Report 2: Results created when Listing 3 is run
Report2 shows the results are sorted based only on the StateName
column. The CityName
column has not been sorted because the code in Listing 3 only specified to sort based on the StateName
column. In order to also sort the CityName
column, the code would have had to sort on that column as well. Multiple column sorts is covered later in the article.
Sorting in ascending or descending order
SQL Server supports two different sort orders: ASC
, and DESC
. Where ASC
stands for ascending and DESC
stands for descending. You can not specify ascending or descending spelled out, you can only specify the abbreviations of ASC
, or DESC
. When no sort order is identified in the ORDER
BY
clasue, like in Listing 3, SQL Server uses the default sort order which is ascending. The code in Listing 4 produces the same results as Listing 3. But in Listing 4 the code tells SQL Server to use ascending sort order, because ASC was specified after the StateName
column.
Listing 4: Sort order of ASC specified
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName From TestData ORDER BY StateName ASC; |
The results could be sorted in descending order by specifying DESC
next to the StateName
column in the ORDER
BY
clause, as has been done in Listing 5.
Listing 5: Sorting results based on StateName in descending order
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName From TestData ORDER BY StateName DESC; |
The code in Listing 5 produces results shown in Report 3.
Report 3: The results from running Listing 5
You may have noticed that the StateName
column contains some state names where the first character of the name is in uppercase and others in lowercase. You may be wondering why these values with similar cases where not sorted together. This is because they are sorted based on the collation of the StateName
column, which, in this case, is case insensitive.
Collation setting of TestData table columns
The collation of the columns in my TestData
table in tempdb is SQL_Latin1_General_CP1_CI_AS. This is because my tempdb got a collation of SQL_Latin1_General_CP1_CI_AS when it was created, and I didn’t override the collation settings when creating my TestData
table. Keep in mind not every tempdb database will have the same collation SQL_Latin1_General_CP1_CI_AS. The tempdb database is created each time SQL Server starts up. The collation setting of tempdb is created based on the collation of the model database. If your tempdb collation setting is not SQL_Latin1_General_CP1_CI_AS then you might see different sorting results when running any of the code in this article. To verify the collation setting of tempdb the code in Listing 6 can be executed.
Listing 6: Display the collation of tempdb
1 |
SELECT DATABASEPROPERTYEX('tempdb','collation'); |
How collation affects sorting
By default, SQL Server sorts character columns based on their column collation. All the TestData
table character columns have a collation of SQL_Latin1_General_CP1_CI_AS. One way to verify the column collation setting is by running the code in Listing 7.
Listing 7: Displaying collation of TestData table columns
1 2 3 4 5 6 7 |
USE tempdb; GO SELECT c.name, c.collation_name FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = 'TestData'; |
Report 4 shows the results when I execute the code in Listing 7. If you have a different collation, you will see different results.
Report 4: Displaying collation of TestData columns
By reviewing the collation_name column, you can see that each character data column in my TestData
table has a collation of SQL_Latin1_General_CP1_CI_AS.
Overriding the sort collation default
In order to override the the default collation setting of a column being sorted the COLLATE
clause can be used. Listing 8 uses the COLLATE
clause to get the columns of the same case to sort together.
Listing 8: Using COLLATE clause
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName FROM TestData ORDER BY StateName COLLATE SQL_Latin1_General_CP1_CS_AS ASC; |
By comparing Report 5 with Report 3, you can see that the state names with same case values are now sorted together when the StateName
column was sorted based on a collation that is case sensitive.
Report 5: Output produce when Listing 8 was executed
Sorting numeric data
You would not think that sorting numbers would be a big deal. They are not when the numbers are stored in a numeric column like the ID
column found in my TestData
table. When a numeric value is stored in a character column, like the CharID
column in my sample data, the results may not be sorted according to numeric order. To demonstrate this, execute the code in Listing 9.
Listing 9: Sorting a character column that contains numeric data
1 2 3 4 |
USE tempdb; GO SELECT CharID, StateName, CityName FROM TestData ORDER By CharID; |
Report 6 shows the results when Listing 9 is run.
Report 6: Output from Listing 9
In Report 6, the numeric values in column CharID
do not seem to be sorted correctly. Meaning the values 11, and 12 appear before the value 2. This is the correct sort order for character data, but not for numeric data. In order to get numeric data stored in a character column to sort correctly based on the numeric values, all that needs to be done is to use the CAST
function in the ORDER
BY
clause, as shown in Listing 10.
Listing 10: Using CAST function in ORDER BY clause
1 2 3 4 |
USE tempdb; GO SELECT CharID, StateName, CityName FROM TestData ORDER By CAST(CharID as TINYINT); |
By using the CAST
function to covert the CharID
column to a numeric data type of tinyint
, SQL Server will sort the StateName
column, based on its casted numeric representation. I’ll leave it up to you to run this code to verify that data is now sorted correctly for numeric values. Listing 10 also shows how a function or expression can be used in the ORDER
BY
clause.
Sorting based on column alias name
Alias names are also supported by the ORDER
BY
clause. This is especially useful when the sort order needs to be based on concatenating a couple of columns together, as shown in Listing 11.
Listing 11: Using alias name in ORDER BY clause
1 2 3 4 |
USE tempdb; GO SELECT CityName + ', ' + StateName as 'City and State' FROM TestData ORDER BY 'City and State'; |
Report 7 shows the output when Listing 11 is executed.
Report 7: Ouput from Listing 11
Sorting based on ordinal position
The ORDER
BY
clause also supports sorting based on an ordinal position. An ordinal position is a numeric value that represents the column position in the set being sorted. In Listing 12, a set with two columns (StateName
and CityName
) is being sorted based on ordinal postion 2, which in this case is the CityName
column.
Listing 12: Sorting based on ordinal position
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName FROM TestData ORDER By 2; |
When the code in Listing 12 is executed, it produces the results in Report 8.
Report 8: Output when Listing 12 is run
Sorting based on multiple columns
All the examples so far have only showed how to sort a result set based on a single column. SQL Server also supports sorting data based on multiple columns. The code in Listing 13 shows how to sort my TestData
using both the StateName
, and CityName
columns.
Listing 13: Sorting based on multiple columns
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName FROM TestData ORDER By StateName, CityName; |
Report 9 shows the output produced when Listing 13 is run.
Report 9: Ouput from Listing 13
The data is now sorted based on StateName
and CityName
columns in ascending order.
Sorting data in both descending and ascending order
When sorting based on multiple columns, SQL Server allows sorting each column using a different sort order. Listing 14 displays StateName
and CityName
values, where StateName
is sorted in descending order and CityName
is sorted in ascending order.
Listing 14: Sorting multiple columns using different sort orders
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName FROM TestData ORDER By StateName DESC, CityName ASC; |
Report 10 can be used to verify that StateName
and CityName
are sorted in DESC
and ASC
order, respectively.
Report 10: Output when Listing 14 is executed
Sorting data based on columns not in select list
Columns used in the ORDER
BY
clause don’t have to be included in the selection list. To demonstrate this the code in Listing 15 can be run.
Listing 15: Sorting based on a column not in the selection list
1 2 3 4 |
USE tempdb; GO SELECT StateName, CityName FROM TestData ORDER By Founded; |
The output in Report 11 is generated when the code in Listing 15 is executed. These results are sorted based on the column Founded
, which is not in the selection list.
Report 11: Results when Listing 15 is run
To verify the results are the order based on the Founded
column value the code in Listing 16 can be run.
Listing 16: Code to identify when cities are founded
1 2 3 4 |
USE tempdb; GO SELECT Founded, StateName, CityName FROM TestData ORDER By Founded; |
I’ll leave it up to you to run the code in Listing 16 to determine the dates that each city was founded.
Using offset fetch to return a subset
The ORDER
BY
clause also supports returning only a subset of rows from a sorted set by using the offset fetch option. To identify the subset of rows to display an OFFSET
and FETCH
value needs to be identified. The OFFSET
value tells how many rows in the sorted set should be skipped before rows are returned. Whereas the FETCH
option identifies the number of rows to return. The code in Listing 17 shows how to use the offset fetch option of the ORDER
BY
clause.
Listing 17: Using the Offset Fetch Option
1 2 3 4 5 |
USE tempdb; GO SELECT StateName, CityName, Founded FROM TestData ORDER BY Founded OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY; |
Report 12 shows the results when Listing 17 is executed.
Report 12: Output produced by Listing 17
The numeric value for OFFSET
and FETCH
options can be provided as expressions. Using an expressions and the offset fetch option is a great method of paging through a sorted set a few records at a time.
Limitations and restrictions
There are a number of limitations when using the ORDER
BY
clause. Below are a few of the common limitations worth noting:
- The
ORDER
BY
clause is not valid in views, inline table-valued functions, derived tables, or sub-queries unless you also use theTOP
clause in your statement. - The combined size of all columns being sorted can not exceed 8060 bytes in length.
- If a top-level query contains a
UNION
,EXCEPT
orINTERSECT
clause then theORDER
BY
clause can only appear at the end of the statement. - Just like any other clause, when a table name is aliased in the FROM clause, only that table alias name can be referenced in the
ORDER
BY
clause.
For a complete list of limitations review the limitation section in the Microsoft Documenation.
Ordering a result set
Ordering a result set in a meaningful way makes it easier to review the returned data. While sorting data seems like a simple concept, in reality there are many nuances that need to be considered when sorting data, like default sort order, column collation settings, numerical data and use of aliases to name a few. Hopefully, this article provided enough examples to help out next time a result set needs to be produced in just the right sorted order.
Load comments