By: Tim Wiseman | Updated: 2016-08-10 | Comments | Related: More > T-SQL
ProblemI 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?
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
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.
- Check out these resources:
Last Updated: 2016-08-10
About the author
View all my tips