18 March 2022

9364 views

13 0

Ordering a result set in SQL Server

To guarantee the order of a result set, you must use an ORDER BY clause. In this article, Greg Larsen explains what you need to know about ORDER BY.

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.

An image showing the syntax of ORDER BY

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

-- Create test data

USE tempdb;

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.

An image showing the results of the query. The rows are in no particular order.

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

USE tempdb;

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

An image showing the results of the query. The rows with Oregon in the state show up first.

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

USE tempdb;

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

USE tempdb;

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

An image showing the results of the query. The rows with Washington show up first.

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

USE tempdb;

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

An image showing the column names and collation of the character 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

USE tempdb;

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

An image showing the results where the upper case items show up first.

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

USE tempdb;

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

An image showing that the CharID column sorts all 1s before 2s.

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

USE tempdb;

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

USE tempdb;

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

An image showing the results when the column alias is used in the ORDER BY clause.

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

USE tempdb;

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

An image showing the data is sorted by ordinal position.

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

USE tempdb;

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

An image showing that the ORDER BY can contain multiple columns.

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

USE tempdb;

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

An image showing that one column can be descending and the other ascending.

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

USE tempdb;

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

An image showing that the ORDER BY can contain a column not showing up in the SELECT list.

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

USE tempdb;

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

USE tempdb;

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

An image showing that by including the Founded column, you can see that it's sorted by that.

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 the TOP 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 or INTERSECT clause then the ORDER 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.