{"id":93541,"date":"2022-03-18T20:21:51","date_gmt":"2022-03-18T20:21:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93541"},"modified":"2022-03-21T15:28:20","modified_gmt":"2022-03-21T15:28:20","slug":"ordering-a-result-set-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/ordering-a-result-set-in-sql-server\/","title":{"rendered":"Ordering a result set in SQL Server"},"content":{"rendered":"

A result set is easier to view when it\u2019s ordered, because the data can be browsed in a meaningful way. SQL Server does not guarantee a result set is ordered unless an ORDER<\/code> BY<\/code> clause is used. Data can be sorted in ascending and\/or descending order based on one or more columns when you use an ORDER<\/code> BY<\/code> clause. This articles explains different ways to use the ORDER<\/code> BY<\/code> clause for ordering a result set.<\/p>\n

Syntax of the ORDER BY clause<\/h2>\n

Figure 1 contains the syntax for the ORDER<\/code> BY<\/code> clause as found in the Microsoft Documentation<\/a>.<\/p>\n

\"An<\/p>\n

Figure 1: ORDER BY syntax<\/strong><\/p>\n

Test data<\/h2>\n

This article will cover a number of examples to show the different ways to use the ORDER<\/code> BY<\/code> clause. Each of these examples will select data from a table named TestData. <\/em>If you want to follow along and run these examples, you can created this table by using the code in Listing 1.<\/p>\n

Listing 1: Code to create TestData<\/em> table<\/strong><\/p>\n

-- Create test data\r\nUSE tempdb;\r\nGO\r\nCREATE TABLE TestData (\r\nID INT, \r\nCharID CHAR(2),\r\nCityName VARCHAR(20),\r\nStateName VARCHAR(20),\r\nFounded SMALLINT);\r\n--Insert rows of test data\r\nINSERT INTO TestData VALUES\r\n(1,'1','Seattle','Washington',1851),\r\n(11,'11','Redmond','washington',1871),\r\n(12,'12','Bellevue','Washington',1953),\r\n(2,'2','Portland','oregon',1851),\r\n(5,'5','Grants Pass','Oregon',1887),\r\n(6,'6','Spokane','washington',1881),\r\n(7,'7','Salem','oregon',1842),\r\n(8,'9','Bend','Oregon',1905),\r\n(9,'9','Tacoma','Washington',1872);<\/pre>\n
Sorting based on a single column<\/h2>\n
If a SELECT<\/code> statement is run without and ORDER<\/code> BY<\/code> 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<\/code> BY<\/code> clause is needed. The code in Listing 2 does not have a ORDER<\/code> BY<\/code> clause, and produces the unordered results found in Report 1.<\/p>\n
Listing 2: SELECT statement without an ORDER BY clause<\/strong><\/p>\n
SELECT StateName, CityName FROM TestData; <\/pre>\n
Report 1: Unsorted Output when Listing 2 is run.<\/strong><\/p>\n
\"An<\/p>\n
The ORDER<\/code> BY<\/code> clause will sort data based on one or more columns. The code in Listing 2 selects the StateName<\/code>, and <\/em>CityName<\/code> columns from the TestData<\/code> table and sorts the results set based on the single column StateName<\/code>.<\/p>\n
Listing 3: Sort based on single column <\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName From TestData\r\nORDER BY StateName;<\/pre>\n
Report 2 shows the results when Listing 3 is executed.<\/p>\n
Report 2: Results created when Listing 3 is run<\/strong><\/p>\n
\"An<\/p>\n
Report2 shows the results are sorted based only on the StateName<\/code> column. The CityName<\/code> column has not been sorted because the code in Listing 3 only specified to sort based on the StateName<\/code> column. In order to also sort the CityName<\/code> column, the code would have had to sort on that column as well. Multiple column sorts is covered later in the article.<\/p>\n
Sorting in ascending or descending order<\/h2>\n
SQL Server supports two different sort orders: ASC<\/code>, and DESC<\/code>. Where ASC<\/code> stands for ascending and DESC<\/code> stands for descending. You can not specify ascending or descending spelled out, you can only specify the abbreviations of ASC<\/code>, or DESC<\/code>. When no sort order is identified in the ORDER<\/code> BY<\/code> 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<\/code> column.<\/p>\n
Listing 4: Sort order of ASC specified<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName From TestData\r\nORDER BY StateName ASC;<\/pre>\n
The results could be sorted in descending order by specifying DESC<\/code> next to the StateName<\/code> column in the ORDER<\/code> BY<\/code> clause, as has been done in Listing 5.<\/p>\n
Listing 5: Sorting results based on StateName <\/em>in descending order<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName From TestData\r\nORDER BY StateName DESC;<\/pre>\n
The code in Listing 5 produces results shown in Report 3.<\/p>\n
Report 3: The results from running Listing 5<\/strong><\/p>\n
\"An<\/p>\n
You may have noticed that the StateName<\/code> 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<\/code> column, which, in this case, is case insensitive.<\/p>\n
Collation setting of TestData<\/em> table columns<\/h2>\n
The collation of the columns in my TestData<\/code> table in tempdb <\/em>is SQL_Latin1_General_CP1_CI_AS<\/em>. This is because my tempdb <\/em>got a collation of SQL_Latin1_General_CP1_CI_AS<\/em> when it was created, and I didn\u2019t override the collation settings when creating my TestData<\/code> table. Keep in mind not every tempdb <\/em>database will have the same collation SQL_Latin1_General_CP1_CI_AS<\/em>. The tempdb<\/em> database is created each time SQL Server starts up. The collation setting of tempdb<\/em> is created based on the collation of the model database. If your tempdb <\/em>collation setting is not SQL_Latin1_General_CP1_CI_AS<\/em> then you might see different sorting results when running any of the code in this article. To verify the collation setting of tempdb <\/em>the code in Listing 6 can be executed.<\/p>\n
Listing 6: Display the collation of tempdb<\/em><\/strong><\/p>\n
SELECT DATABASEPROPERTYEX('tempdb','collation');<\/pre>\n
How collation affects sorting<\/h2>\n
By default, SQL Server sorts character columns based on their column collation. All the TestData<\/code> table character columns have a collation of SQL_Latin1_General_CP1_CI_AS<\/em>. One way to verify the column collation setting is by running the code in Listing 7.<\/p>\n
Listing 7: Displaying collation of TestData <\/em>table columns<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT c.name, \r\n       c.collation_name\r\n  FROM SYS.COLUMNS c\r\n  JOIN SYS.TABLES t ON t.object_id = c.object_id\r\n WHERE t.name = 'TestData';<\/pre>\n
Report 4 shows the results when I execute the code in Listing 7. If you have a different collation, you will see different results.<\/p>\n
Report 4: Displaying collation of TestData <\/em>columns<\/strong><\/p>\n
\"An<\/p>\n
By reviewing the collation_name<\/em> column, you can see that each character data column in my TestData<\/code> table has a collation of SQL_Latin1_General_CP1_CI_AS.<\/em><\/p>\n
Overriding the sort collation default<\/h2>\n
In order to override the the default collation setting of a column being sorted the COLLATE<\/code> clause can be used. Listing 8 uses the COLLATE<\/code> clause to get the columns of the same case to sort together.<\/p>\n
Listing 8: Using COLLATE clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName FROM TestData\r\nORDER BY StateName COLLATE SQL_Latin1_General_CP1_CS_AS ASC;<\/pre>\n
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<\/code> column was sorted based on a collation that is case sensitive.<\/p>\n
Report 5: Output produce when Listing 8 was executed<\/strong><\/p>\n
\"An<\/p>\n
Sorting numeric data<\/h2>\n
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<\/code> column found in my TestData<\/code> table. When a numeric value is stored in a character column, like the CharID<\/code> column in my sample data, the results may not be sorted according to numeric order. To demonstrate this, execute the code in Listing 9.<\/p>\n
Listing 9: Sorting a character column that contains numeric data<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT CharID, StateName, CityName FROM TestData\r\nORDER By CharID;<\/pre>\n
Report 6 shows the results when Listing 9 is run.<\/p>\n
Report 6: Output from Listing 9<\/strong><\/p>\n
\"An<\/p>\n
In Report 6, the numeric values in column CharID<\/code> do not seem to be sorted correctly. Meaning the values 11<\/em>, and 12<\/em> appear before the value 2<\/em>. 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<\/code> function in the ORDER<\/code> BY<\/code> clause, as shown in Listing 10.<\/p>\n
Listing 10: Using CAST function in ORDER BY clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT CharID, StateName, CityName FROM TestData\r\nORDER By CAST(CharID as TINYINT);<\/pre>\n
By using the CAST<\/code> function to covert the CharID<\/code> column to a numeric data type of tinyint<\/code>, SQL Server will sort the StateName<\/code> column, based on its casted numeric representation. I\u2019ll 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<\/code> BY<\/code> clause.<\/p>\n
Sorting based on column alias name<\/h2>\n
Alias names are also supported by the ORDER<\/code> BY<\/code> 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.<\/p>\n
Listing 11: Using alias name in ORDER BY clause<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT CityName + ', ' + StateName as 'City and State' FROM TestData \r\nORDER BY 'City and State';<\/pre>\n
Report 7 shows the output when Listing 11 is executed.<\/p>\n
Report 7: Ouput from Listing 11<\/strong><\/p>\n
\"An<\/p>\n
Sorting based on ordinal position<\/h2>\n
The ORDER<\/code> BY<\/code> 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<\/code> and CityName<\/code>) <\/em>is being sorted based on ordinal postion 2, which in this case is the CityName<\/code> column.<\/p>\n
Listing 12: Sorting based on ordinal position<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName FROM TestData\r\nORDER By 2;<\/pre>\n
When the code in Listing 12 is executed, it produces the results in Report 8.<\/p>\n
Report 8: Output when Listing 12 is run<\/strong><\/p>\n
\"An
\n<\/strong><\/p>\n
Sorting based on multiple columns<\/h2>\n
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<\/code> using both the StateName<\/code>, and CityName<\/code> columns.<\/p>\n
Listing 13: Sorting based on multiple columns<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName FROM TestData\r\nORDER By StateName, CityName;<\/pre>\n
Report 9 shows the output produced when Listing 13 is run.<\/p>\n
Report 9: Ouput from Listing 13<\/strong><\/p>\n
\"An<\/p>\n
The data is now sorted based on StateName<\/code> and CityName<\/code> columns in ascending order.<\/p>\n
Sorting data in both descending and ascending order<\/h2>\n
When sorting based on multiple columns, SQL Server allows sorting each column using a different sort order. Listing 14 displays StateName<\/code> and CityName<\/code> values, where StateName<\/code> is sorted in descending order and CityName<\/code> is sorted in ascending order.<\/p>\n
Listing 14: Sorting multiple columns using different sort orders<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName FROM TestData\r\nORDER By StateName DESC, CityName ASC;<\/pre>\n
Report 10 can be used to verify that StateName<\/code> and CityName<\/code> are sorted in DESC<\/code> and ASC<\/code> order, respectively.<\/p>\n
Report 10: Output when Listing 14 is executed<\/strong><\/p>\n
\"An<\/p>\n
Sorting data based on columns not in select list<\/h2>\n
Columns used in the ORDER<\/code> BY<\/code> clause don\u2019t have to be included in the selection list. To demonstrate this the code in Listing 15 can be run.<\/p>\n
Listing 15: Sorting based on a column not in the selection list <\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName FROM TestData\r\nORDER By Founded;<\/pre>\n
The output in Report 11 is generated when the code in Listing 15 is executed. These results are sorted based on the column Founded<\/code>, which is not in the selection list.<\/p>\n
Report 11: Results when Listing 15 is run<\/strong><\/p>\n
\"An<\/p>\n
To verify the results are the order based on the Founded<\/code> column value the code in Listing 16 can be run.<\/p>\n
Listing 16: Code to identify when cities are founded<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT Founded, StateName, CityName FROM TestData\r\nORDER By Founded;<\/pre>\n
I\u2019ll leave it up to you to run the code in Listing 16 to determine the dates that each city was founded.<\/p>\n
Using offset fetch to return a subset<\/h2>\n
The ORDER<\/code> BY<\/code> 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<\/code> and FETCH<\/code> value needs to be identified. The OFFSET<\/code> value tells how many rows in the sorted set should be skipped before rows are returned. Whereas the FETCH<\/code> option identifies the number of rows to return. The code in Listing 17 shows how to use the offset fetch option of the ORDER<\/code> BY<\/code> clause.<\/p>\n
Listing 17: Using the Offset Fetch Option<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT StateName, CityName, Founded FROM TestData\r\nORDER BY Founded\r\nOFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;<\/pre>\n
Report 12 shows the results when Listing 17 is executed.<\/p>\n
Report 12: Output produced by Listing 17<\/strong><\/p>\n
\"An<\/p>\n
The numeric value for OFFSET<\/code> and FETCH<\/code> 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.<\/p>\n
Limitations and restrictions<\/h2>\n
There are a number of limitations when using the ORDER<\/code> BY<\/code> clause. Below are a few of the common limitations worth noting:<\/p>\n