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 BY clause 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 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.

Here is the syntax for the ORDER BY clause:

ORDER BY

order_by_expression

[COLLATE collation_name]

[ASC | DESC][,…N]

Where:

• order_by_expression identifies 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_name identified 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 BY clause 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 clauses to retrieve rows from these tables. If you want to follow along and execute the SELECT statements in this article you can create my sample tables by running the code in Listing 1.

USE tempdb;
GO
SET NOCOUNT ON;
-- Create sample tables
-- Create table Car
CREATE TABLE [dbo].[Car](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Make] [varchar](50) NULL,
   [Model] [varchar](50) NULL,
   [NumOfDoors] [tinyint] NULL,
   [ModelYear] [smallint] NULL,
   [ColorId] [int] NULL
) ON [PRIMARY]
-- Create Table Color
CREATE TABLE [dbo].[Color](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Color] [varchar](50) NULL
) ON [PRIMARY];
-- Populate Color table
INSERT INTO Color VALUES('Red');
INSERT INTO Color VALUES('White');
INSERT INTO Color VALUES('Blue');
INSERT INTO Color VALUES('Green');
-- Populate Car Table
INSERT INTO Car VALUES ('Ford','Mustang','2','1964',1);
INSERT INTO Car VALUES ('ford','F150','2','2010',1);
INSERT INTO Car VALUES ('Toyota','Camry','4','2011',1);
INSERT INTO Car VALUES ('Ford','Taurus','5','1995',2);
INSERT INTO Car VALUES ('ford','F250','2','2010',3);
INSERT INTO Car VALUES ('Chevrolet','Volt','4','2010',1);
INSERT INTO Car VALUES ('Ford','Focus','4','2012',4);
INSERT INTO Car VALUES ('Chevrolet' ,'Aveo','4','2011',2);
INSERT INTO Car VALUES ('Chevrolet','Camaro','4','1978',4);
INSERT INTO Car VALUES ('Honda','Civic','4','2012',1);
INSERT INTO Car VALUES ('Chevrolet','Cruse','4','2012',1);
INSERT INTO Car VALUES ('Toyota','Rav4','5','2000',1);

Listing 1: Script to create sample tables

This script creates two tables, Car and Color in 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 SELECT statement without an ORDER BY clause 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 BY clause 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.

USE tempdb;
GO        
SELECT Car.Make 
   ,Car.Model
   ,Car.NumOfDoors
   ,Car.ModelYear
   ,Color.Color
FROM Car INNER JOIN Color
  ON Car.ColorId = Color.ID
ORDER BY Car.Make ASC;

Listing 2: SELECT statement that orders data by Car.Make

When I run the code in Listing 2 I get the output shown in Report 1. By reviewing this report you can see that all the rows are returned in alphabetic or ascending order based on the Car.Make column.

Report 1: Output when ordering by Car.Make

Notice that I didn’t specify the ASC 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 COLLATE clause 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.

USE tempdb;
GO        
SELECT Car.Make 
   ,Car.Model
   ,Car.NumOfDoors
   ,Car.ModelYear
   ,Color.Color
FROM Car INNER JOIN Color
  ON Car.ColorId = Color.ID 
  ORDER BY Car.Make 
  COLLATE SQL_Latin1_General_CP1_CS_AS ASC;

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.

USE tempdb;
GO        
SELECT Car.Make 
   ,Car.Model
   ,Car.NumOfDoors
   ,Car.ModelYear
   ,Color.Color
FROM Car INNER JOIN Color
  ON Car.ColorId = Color.ID
ORDER BY Car.ModelYear DESC;

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

When I run the code in Listing 4 I get the output in Report 2.

Report 2: Output in descending order by Car.Model

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 BY clause.

USE tempdb;
GO        
SELECT Car.Make 
   ,Car.Model
   ,Car.NumOfDoors
   ,Car.ModelYear
   ,Color.Color
FROM Car INNER JOIN Color
  ON Car.ColorId = Color.ID
ORDER BY 4 DESC;

Listing 6: Ordering data using a column reference number

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 BY clause 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.Make in ascending order, and the Car.ModelYear in descending order.

USE tempdb;
GO        
SELECT Car.Make 
   ,Car.Model
   ,Car.NumOfDoors
   ,Car.ModelYear
   ,Color.Color
FROM Car INNER JOIN Color
  ON Car.ColorId = Color.ID
ORDER BY Car.Make ASC
        , Car.ModelYear DESC;

Listing 7: Ordering data using multiple columns

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.

USE tempdb;
GO        
CREATE TABLE NumericTest (Name VARCHAR(20), Number VARCHAR(4));
INSERT INTO NumericTest VALUES ('One','1');
INSERT INTO NumericTest VALUES ('One Thousand','1000');
INSERT INTO NumericTest VALUES ('Two','2');
INSERT INTO NumericTest VALUES ('Twenty' ,'20');
INSERT INTO NumericTest VALUES('Ten','10');
SELECT * FROM NumericTest
ORDER BY Number;
DROP TABLE NumericTest;

Listing 8: Sorting numeric data stored in a varchar column

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.

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.

USE tempdb;
GO        
CREATE TABLE NumericTest (Name VARCHAR(20), Number VARCHAR(4));
INSERT INTO NumericTest VALUES ('One','1');
INSERT INTO NumericTest VALUES ('One Thousand','1000');
INSERT INTO NumericTest VALUES ('Two','2');
INSERT INTO NumericTest VALUES ('Twenty' ,'20');
INSERT INTO NumericTest VALUES('Ten','10');
SELECT * FROM NumericTest
ORDER BY CAST(Number AS INT);
DROP TABLE NumericTest;

Listing 9 Sorting numeric data stored in varchar column

In Listing 9 I used the CAST function in the ORDER BY clause 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.