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.