Different Methods to Sort SQL Server Result Sets

By: Tim Wiseman   |   Updated: 2016-08-10   |   Comments   |   Related: > TSQL

Problem

Solution

By default, the order of a result set in SQL Server is not guaranteed. While the order the rows are returned in is often fairly consistent, a programmer should not rely on this without explicitly directing the SQL Server to order them. T-SQL allows the order to be specified with an order by clause.

An order by clause allows the results to be sorted by an expression. Frequently, this is a column or columns from the query, but it does not have to be. A result set can be ordered by columns that are not actually returned or by a more complicated method. This can allow rows to be ordered in ways that are more complex than simply sorting alphabetically or numerically by a column.

Create Sample SQL Server Data Set

So that we can look at some examples, let's create a table that lists several of the recent Medal of Honor winners from the Army:

create table dbo.MedalOfHonor (
 Id int identity (1,1) primary key,
 MilRank varchar(50),
 SoldierName varchar(50),
 YearOfAction varchar(4)
 )

insert into dbo.MedalOfHonor (MilRank, SoldierName, YearOfAction)
values
 ('Staff Sergeant', 'Ty Carter', '2009'),
 ('Captain', 'Florent Groberg', '2012'),
 ('Major', 'William D. Swenson', '2009'),
 ('Sergeant First Class', 'Jared C. Monti', '2006'),
 ('Master Sergeant', 'Leroy Petry', '2008'),
 ('Sergeant', 'Kyle White', '2007'),
 ('Specialist', 'Ross A. McGinnis', '2006')

Simple SQL Server Sort Example

We may want to sort the data by military rank. If we use an order by clause to sort on the MilRank column, it will sort it alphabetically.

select 
    MilRank, SoldierName, YearOfAction
from 
    dbo.MedalOfHonor
order by 
    MilRank

Here we can see the data has been sorted by MilRank.

Sorting SQL Server Results Using an Unreturned Column

To sort it by the actual rank and not the rank name, we can create another table that provides the ordering data. If this is something that will be used frequently, it makes sense to actually create a new permanent table to hold this data. If this will be a one time usage, we may prefer to temporary table or table variable. In this case we can do something like this:

--This would be more complicated with more Services, but only using the Army here
create table dbo.RankToPayGrade (
 MilRank varchar(50) primary key, 
 PayGrade varchar(4) )

--For space reasons, only listing the ranks that appear in the MedalOfHonor Table
insert into dbo.RankToPayGrade (MilRank, PayGrade)
values
 ('Staff Sergeant', 'E-6'),
 ('Captain', 'O-3'),
 ('Major', 'O-4'),
 ('Sergeant First Class', 'E-7'),
 ('Master Sergeant', 'E-8'),
 ('Sergeant', 'E-5'),
 ('Specialist', 'E-4')

select 
 M.MilRank, M.SoldierName, M.YearOfAction
from
 dbo.MedalOfHonor M
 left join dbo.RankToPayGrade R on R.MilRank = M.MilRank
order by
 R.PayGrade

The data has been sorted by the PayGrade column, even though this is not part of the result set.

SQL Server Sort Using a Function

Since SQL Server allows full expressions in the order by clause, we can use functions in it as well. Our MedalOfHonor Table does not break out first and last name, so if we want to sort by the last name we need to use a function to extract that. Of course, this example is a bit simplistic since it assumes that the last name does not have a space in it, but as long as we can make that assumption it could look like:

--This ordering will be inaccurate if a last name contains a space.
select 
 M.MilRank, M.SoldierName, M.YearOfAction
from
 dbo.MedalOfHonor M
order by
 SUBSTRING(M.SoldierName, len(M.SoldierName) - charindex(' ', REVERSE(M.SoldierName))+2, len(M.SoldierName))

Here is the results sorted by last name.

SQL Server Sort Using a Column Alias

The order by clause will take a column alias, so if we wanted to display the last name, we could simplify somewhat with this:

--This will be inaccurate if a last name contains a space.
select 
 SUBSTRING(M.SoldierName, len(M.SoldierName) - charindex(' ', REVERSE(M.SoldierName))+2, len(M.SoldierName)) as LastName,
 M.YearOfAction, M.MilRank 
from
 dbo.MedalOfHonor M
order by
 LastName

Conclusions

The order by clause liberally accepts SQL expressions. This means that if an ordering of a varchar column is desired other than alphabetical there are a couple of ways of achieving it. The sort order can be determined by another column with the query ordered by that other column whether it appears in the result set or not. The information for ordering can also be calculated through functions and provided that way to the order by clause.

Next Steps

• Check out these resources:
  • SQL Server SELECT with an ORDER BY
  • Use WHERE, JOIN, ORDERBY, SELECT Column Order When Creating Indexes
  • SQL Server Indexing Tips

About the author

Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips