Stairway to T-SQL DML Level 6: The Basics of Sorting Data Using the ORDER BY Clause

The Series

This Stairway will provide you with a basic understanding of how to work with data from SQL Server tables, using SQL Server’s Transact-SQL (T-SQL) dialect. DML is the Data Manipulation Language, and is the aspect of the language dealing with the data. It includes the statements SELECT, INSERT, UPDATE and DELETE. This Stairway will as also provide some history of the SQL language and some general concepts about set theory. Each level will build upon the prior level, so by the time you are finished you will have a good understanding of how to select and modify data from SQL Server.

Back in level 1 I showed you how to select rows from your SQL Server tables, and then in Level 4 and 5 I introduced you to joining tables and rows together using the INTERSECT, UNION, EXCEPT, and JOIN operators. In those articles, the SELECT statement examples all returned data in an arbitrary order. Sometimes you will need your returned row set sorted in a particular order so you can more easily examine the results looking for specific data. In this Level I will be showing you how to use the ORDER BY clause to return your data in a sorted order.

Ordering your records

When you add the ORDER BYclause to your SELECT statement SQL Server usually uses the SORT operator to organize the returned result set. If you happen to be sorting your result set by the clustered index key then a SORT operator may not be needed. You can use the ORDER BY clause to sort your data in ascending or descending order based on a column, a set of columns, or an expression. The ORDER BYclause is not valid in views, inline table-valued functions, derived tables, or sub-queries unless you also use the TOP clause in your statement.

Here is the syntax for the ORDER BY clause:

ORDER BY

order_by_expression

[COLLATE collation_name]

[ASC | DESC][,…N]

Where:

order_by_expressionidentifies the a sort criteria. The sort criteria can be a column name, column alias name, an integer that represents the position of the column in the selection list, or an expression.

collation_nameidentified the collation that will be used when sorting specified column. If no COLLATE parameter is used then the column is sorted based on the collation associated with the columns used in the order_by_expression.

ASC or DESC represents the way the order_by_expressionwill be sorted. ASC stands for ascending sequence and DESC stands for descending sequence. If neither ASC nor DESC is specified then the order_by_expression is sorted in in ascending sequence.

To demonstrate how to use the ORDER BYclause I first will create a couple of tables and then populate them with several rows of data. I will then execute a number of different SELECT statements that contain different ORDER BY clausesto retrieve rows from these tables. If you want to follow along and execute the SELECTstatements in this article you can create my sample tables by running the code in Listing 1.

This script creates two tables, Car and Colorin the tempdb database. Each of my examples in this article will use one or both of these tables to demonstrate how to use the ORDER BY to sort your result set.

Ordering by a Single Column

A basic SELECTstatement without an ORDER BYclause will return records in an arbitrary order, but might actually be ordered if an index was used to retrieve the records. However, we can never be sure of the order that the rows will be returned without ORDER BY. If I need my results set to be returned in a specific order I need to add the ORDER BYclause to my SELECT statement and specify the column or columns I want SQL Server to sort on. For my first example, found in Listing 2, I will sort my data on Car.Make in ascending sequence.

Notice that I didn’t specify theASC or DESC in my ORDER BY specification. By not specifying the sort direction, SQL Server will sort in ascending (ASC) order.

Sorting Data based on Collation

If you look closely at the output in Report 1 you will notice that the first character in the Make column has both upper and lower case spelling for the value “Ford”.Since the Make column in table Car has a collation setting of case insensitive in my tempdb database, the upper and lower case spellings of “Ford” are interleaved in the output. This happened because the ORDER BY follows the sorting rules associated with collation settings of columns specified in the ORDER BY clause. If I want to have my output sorted based on case, so all the upper case Ford’s would be together, and the lower case ford’s would be next to each other, I would need to use the COLLATE options associated with the ORDER BY clause. In Listing 3 I have a SELECT statement that uses the COLLATEclause as part of the ORDER BY specification. This SELECT statement will return the same rows shown in Report 1 but the returned rows will now be sorted based on case.

Listing 3: SELECT statement that orders data based on case sensitivity by Car.Make

I’ll leave it up to you to run the SELECT statement in Listing 3 to show how the cars with a make of “Ford” will be sorted based on case.

Sorting Data in Descending Sequence

There are times you want to see records sorted in an order where the last item alphabetically or numerically appears first in the result set. To do this you need to use the DESC (descending) option of the ORDER BY clause. If I want to see the newest car, by ModelYear at the top of my result set I could order my result set in descending sequence by Model Year. This is what I have done in Listing 4.

By reviewing this output you can see that all the cars with ModelYear of 2012 are at the top of the list of in my results.

Sorting Data Based on Column Not In SELECT List

SQL allows you to sort your result set on columns that are not in the SELECT list. To demonstrate this, I have created the code in Listing 5.

USE tempdb;
GO
SELECT Make
,Model
FROM Car
ORDER BY Car.ModelYear DESC;

Listing 5: Order by Car.ModelYear in descending order.

This code, when run, will return the Make and Model columns sorted by Car.ModelYear, even though the Car.ModelYear is not included in the SELECT list.

Sorting Data based on Column Ordinal Position

Another option for specifying which column to sort on is to use a column ordinal position. By ordinal position I mean the order in which the columns appear in the SELECT list. One reason why you might use the ordinal position in your ORDER BY clause is when the select list column you want to sort on contains a complicated expression, such as a CASE expression. You also need to keep in mind when you do use an ordinal position, that adding additional columns to your select list might require changing the ordinal positions in the ORDER BY clause. If you don’t change the ordinal position value when adding columns to your select list you might get your result set sorted incorrectly. Listing 6 contains a SELECT statement that uses a column ordinal position for the Car.ModelYear column in the ORDER BYclause.

When Listing 6 is run it will return a sorted result set that is exactly the same as results in Report 2.

Sorting Data Based on Multiple Columns

Each of my examples so far has shown you how to order a result set based on a single column. The ORDER BYclause allows you to order record sets based on multiple columns or expressions. You can even specify a different sort direction (ascending, or descending) for each column specified. In Listing 7 I have a SELECT statement that will sort the results based on Car.Makein ascending order, and theCar.ModelYear in descending order.

Numeric Character Data Sorted Alphabetically

When sorting numeric data that is contained in character data type columns the data will be sorted alphabetically. Since data is sorted alphabetically this means each column will be sorted based on the characters in the column from left to right. This means the number 10 will sort before the number 2 because the first digit (1) of the number 10 comes alphabetically before the digit 2. The sample code in Listing 8 demonstrates this behavior.

When this code is run the output in Report 3 is produced. If you look at this output you will see that 10, and 1000 sort before 2 and 20.

NameNumber

-------------------- ------

One1

Ten10

One Thousand1000

Two2

Twenty20

Report 3: Numeric Data Sorted Alphabetically

Since the Number column is defined as a varchar(4) SQL Server sorted this column alphabetically, even though this column only contains numbers. If the Number column had been defined using a numeric data type SQL Server would have sorted these records based on the numeric values in this column. This would have caused 2 to come before 10, and 1000 when sorted in ascending sequence. If you do happen to have numeric data stored in a character field type you can use the trick shown in Listing 9 to resolve the sorting issue demonstrated by Listing 8.

In Listing 9 I used the CAST function in the ORDER BYclause to convert the character data to an integer prior to SQL Server sorting the rows. If you run this code you will see that the numbers are now sorted according to their numeric value. This code also demonstrates how you can use an expression, or function in your ORDER BY clause.

Create Order to Your Selected Data

Using the basic SELECT statement returns your data in an arbitrary order. To guarantee that your data will be returned by SQL Server in a specific order, you need to have an ORDER BY clause. The ORDER BY clause allows you to sort your data by a single column or multiple columns, in descending or ascending order. SQL Server also allows you to use functions or expressions in the ORDER BY clause so calculated values can be used to order your data. Create order to your selected data by using the ORDER BY clause.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.