T-SQL Programming Part 10 – Returning Sorted Record Sets Using the ORDER BY Clause

Having your data returned to you in some meaningful sorted order is important sometimes.  If you don’t tell SQL Server you want to order the results of a SELECT statement then there is no guarantee that your result set will come back in a particular order.  To make sure a result set is ordered you need to use the ORDER BY clause.  In this article I will be exploring how to return an order result set by using the ORDER BY clause.

Test Data for Examples

Before I can demonstrate how to use the ORDER BY clause to sort a result set I need to create some data we can sort.  I will use the code below to create my sample data:

USE tempdb;
GO
DROP TABLE street, city
SET NOCOUNT ON;
CREATE TABLE [dbo].[City](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [City] [varchar](50) NULL
) ON [PRIMARY];
CREATE TABLE [dbo].[Street](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [StreetName] [varchar](50) NULL,
       [StreetType] [varchar](50) NULL,
       [StreetRating] [varchar](2) NULL,
       [CityId] [int] NULL
) ON [PRIMARY]
-- Ppopulate rows in City table
INSERT INTO City VALUES('Seattle'),('Redmond'),('Tacoma'),('Olympia');
-- Populate rows in Street table
INSERT INTO STREET VALUES 
       ('Pike Street','Major',2,1), 
       ('Olive Way','major',2,1),
       ('Interstate 5','Freeway',10,1),
       ('Redmond Way','Major',1,2),
       ('Northeast 80th Street','major',1,2),
       ('North Pearl Street','Major',20,3),
       ('South Jackson Avenue','major',20,3),
       ('Capital Way','Major',5,4);

In the code above I created two tables: City and Street.  These two tables were created in the tempdb database and will be used in the examples below.  If you want to follow along and run my examples you can use this script to create sample data for those examples.

Sorting on a Single Column Value

There are times when you just need to have your data come back in a sorted order based on a single column value.  Suppose I wanted to join together my City and Street tables and return my output ordered by the StreetName.  I could do that by running the following code:

SELECT * FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY StreetName;
 

When I run this code I get the following output:

ID  StreetName                 StreetType          StreetRating CityId      ID       City
--- -------------------------- ------------------- ------------ ----------- -------- ---------
8   Capital Way                Major               5            4           4        Olympia
3   Interstate 5               Freeway             10           1           1        Seattle
6   North Pearl Street         Major               20           3           3        Tacoma
5   Northeast 80th Street      major               1            2           2        Redmond
2   Olive Way                  major               2            1           1        Seattle
1   Pike Street                Major               2            1           1        Seattle
4   Redmond Way                Major               1            2           2        Redmond
7   South Jackson Avenue       major               20           3           3        Tacoma

By adding an ORDER BY clause at the end of my SELECT statement I was able to return sorted results.  My results are sorted based on StreetName in ascending order. 

The default sort order is ascending.  But what if I wanted to sort in descending order.  How would I do that?

The ORDER BY clause allows you to specify whether you want the sorted output in descending or ascending order.  If you want to explicitly tell SQL Server which ordering scheme to use you need to specify ASC, or DESC along with the ORDER BY clause.  Let me show you with the following example:

SELECT* FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY StreetName DESC;

This example is similar to the prior example, but instead my results will be sorted in descending sequence based on StreetName.  Because I added the additional “DESC” option along with the “ORDER BY” clause, I told SQL Server to sort my data in descending order, instead of using the default ascending order.  

Sorting Based on Column Position

You can also identify the column or columns that you want to sort on based on order in which they appear in the SELECT statement.  Below I have an ORDER BY clause that sorts the output based on the second column selected:

SELECT S.StreetName, C.City FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY 2;
 

When I run this code I get the following output:

StreetName                                         City
-------------------------------------------------- ------------------------------------------
Capital Way                                        Olympia
Redmond Way                                        Redmond
Northeast 80th Street                              Redmond
Pike Street                                        Seattle
Olive Way                                          Seattle
Interstate 5                                       Seattle
North Pearl Street                                 Tacoma
South Jackson Avenue                               Tacoma
 
 

By reviewing the output above you can see all rows are sorted by City.  City is the second column in my selection list.  By specifying “ORDER BY 2”, I told SQL Server to order the result set based on the second column in the selection list.  If I was to use the clause “ORDER BY 1” my output would have been sorted by StreetName.  I will leave it up to you to verify that using the “ORDER BY 1” clause does in fact return results in StreetName order. 

Sorting Based on Multiple Columns

There are times when you will need to sort on multiple columns.  To do that is easy, all you need to do is add more columns to your ORDER BY clause.  Below is an example where I sorted my Street and City data by City and StreentName.

SELECT * FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY City, StreetName;
 

When I run the code above I get the following output:

ID  StreetName                 StreetType          StreetRating CityId      ID       City
--- -------------------------- ------------------- ------------ ----------- -------- ---------
8   Capital Way                Major               5            4           4        Olympia
5   Northeast 80th Street      major               1            2           2        Redmond
4   Redmond Way                Major               1            2           2        Redmond
3   Interstate 5               Freeway             10           1           1        Seattle
2   Olive Way                  major               2            1           1        Seattle
1   Pike Street                Major               2            1           1        Seattle
6   North Pearl Street         Major               20           3           3        Tacoma
7   South Jackson Avenue       major               20           3           3        Tacoma

By reviewing the code above you can see my ORDER BY clause has City first followed by StreetName.  This ordering of columns in the ORDER BY clause is important.  The ordering of columns tells SQL Server which column to sort on first, and which one should be second.  You can see this in the sorted output returned above.

Sorting Numbers

You would think sorting by numbers would be easy.  This is true when you are sorting numeric data types.  But what happens when you are sorting character columns that contain numeric values.  To see what happens lets run the following code:

SELECT StreetRating, StreetName, City FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY StreetRating;

When I run this code I get the following output:

StreetRating StreetName                                         City
------------ -------------------------------------------------- ------------------------------
1            Redmond Way                                        Redmond
1            Northeast 80th Street                              Redmond
10           Interstate 5                                       Seattle
2            Pike Street                                        Seattle
2            Olive Way                                          Seattle
20           North Pearl Street                                 Tacoma
20           South Jackson Avenue                               Tacoma
5            Capital Way                                        Olympia

If you review the results above you can see the number “10” in the StreetRating column comes before “2”.  This is not in numerical order.  This is because the StreetName column is not a numeric data type.  If you look at my CREATE TABLE statement at the top of the article you will see the StreetRating column is defined as a VARCHAR(2) column.  Therefore my sort statement above sorted my data based on the character representation of the StreetRating number.   One way to resolve this problem is change the code above to look like this:

SELECT StreetRating, StreetName, City FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY CAST(StreetRating AS TINYINT);

In this code snippet I casted the StreetRating column into a numeric data type in the ORDER BY clause.  In this case a TINYINT.  By doing this SQL Server sorts the data based on the TINYINT data type.  If you run the code above you will see that the output is sorted in the correct numerical sequence.

How Collation can Affect the Sorted Output

If you have been looking closely at the output of StreetType you have seen that the first character for some of the StreetType column values are in lower case and some of them have an upper case character.  Suppose you wanted to sort my street types so all the uppercase types appeared next to each other and the lowercase types appeared together.  If I use the normal ORDER BY on the StreetName column it will sort the street name based on the collation setting of the columns being sorted.   To sort my StreetType in case sensitivity order all I need to do is add the COLLATE clause to the ORDER BY clause as I have done below:

SELECT StreetType FROM 
Street as S
ORDER BY StreetType COLLATE SQL_Latin1_General_CP1_CS_AS ASC

When I run this code the StreetType columns will be sorted based on the collation “SQL_Latin1_Gerneral_CP1_CS_AS” order.  This collation setting is case sensitive so my ORDER BY clause will make the SQL Server engine return a record set that has all StreetType column value with the same case setting appear together. 

Limiting the Number of Rows Returned

Along with the ORDER BY clause you can specify an OFFSET and FETCH clause to return a limited set of rows.  Suppose I wanted to skip the first two rows of an ordered set and then only return 2 rows, I could do that with the following SELECT statement:

SELECT StreetRating, StreetName, City FROM 
Street as S
JOIN 
City as C
on S.CityId = C.ID
ORDER BY StreetRating
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

In this code I included an OFFSET and FETCH clause.  The OFFSET clause specifies the number of rows to skip, whereas the FETCH clause specifies the number of rows to return.  In my query above I will skip the first two rows of the ordered set and then return three rows.    If you run this code you will see that it returns all of StreetRating and StreetName column values for the records that have a City equal to “Seattle”. 

ORDER BY Gotcha’s

There are a number of gotcha’s you need to be aware of when using the ORDER BY clause.  One of those is using the ORDER BY clause to sort on multiple columns, where the combined size of all sorted columns is greater than 8060 characters.  To demonstrate the error that is produced when you do this you can run the following code:

CREATE TABLE BigColumn1 (ID INT, Col1 CHAR(5000));
CREATE TABLE BigColumn2 (ID INT, Col2 CHAR(5000));
INSERT INTO BigColumn1 VALUES  
    (1, REPLICATE('A',5000)), (2, REPLICATE('C',5000));
INSERT INTO BigColumn2 VALUES 
    (1, REPLICATE('B',5000)), (2, REPLICATE('D',5000));
SELECT * FROM 
dbo.BigColumn1 JOIN dbo.BigColumn2
ON BigColumn1.ID = BigColumn2.ID
ORDER BY Col1, Col2;

Another gotcha is when you use table alias names.  If you use a table alias on a table in the FROM clause then you need to use the table alias name when reference columns in the ORDER BY clause. 

For a better understanding of the other things that might cause you frustration while trying to order your data look at the “Limitations and Restrictions” section of the ORDER BY documentation in Books Online: http://msdn.microsoft.com/en-us/library/ms188385.aspx

My Results Are Ordered

There are many times when you want to bring back a result set from SQL Server in sorted order. To accomplish this you use the ORDER BY clause.  I hope this article has helped you understand the different ORDER BY options you can use to make SQL Server return your data in a sorted order that meets your business requirements.    

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles