Different Methods to Sort SQL Server Result Sets


By:   |   Updated: 2016-08-10   |   Comments   |   Related: More > T-SQL

Problem
I have a need to get more creative with the way my SQL Server results sets are returned based on user needs.  Can you explain other ways that I can use the ORDER BY clause to control how data is returned?
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.

Simple SQL Server Sort Example

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.

Sorting SQL Server Results Using an Unreturned Column

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 Function

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

SQL Server Sort Using a Column Alias

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


Last Updated: 2016-08-10


get scripts

next tip button



About the author
MSSQLTips author Tim Wiseman Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools