Order By: Dynamic syntax and other advanced usage

While ORDER BY is one of the most commonly used keywords in SQL server, there are some helpful tricks that are not as well known.

The following is a simplified table for use in the subsequent examples:

  
CREATE TABLE Employee (EmployeeID INT, EmployeeName Varchar(128), Salary MONEY, DateOfHire DATETIME)
GO

Insert Employee VALUES (1, 'Steve Smith', 100000, '2001-01-01')
Insert Employee VALUES (2, 'T.O.', 500000, '2003-01-01')
Insert Employee VALUES (3, 'Donovan McNabb', 8000, '2007-01-01')
Insert Employee VALUES (4, 'Peyton Manning', 1000000, '2008-01-01')
Insert Employee VALUES (5, 'Brett Favre', 232345, '1975-01-01')
Insert Employee VALUES (6, 'Donald Driver', 232345, '1999-01-01')
Insert Employee VALUES (7, 'Tom Brady', 1000000, '2007-01-01')
GO

 

The Basics


Ascending is the default ordering, and does not need to be specified.  These two statements return the same result set:

-- Equivalent
Select * from Employee Order by EmployeeID
Select * from Employee Order by EmployeeID ASC

 

1	Steve Smith		100000.00	2001-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000

 

1	Steve Smith		100000.00	2001-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000

 

The Descending keyword can be specified to change the order, and the two can be combined for use with multiple columns:


Select * from Employee Order by Salary DESC, EmployeeID ASC
Select * from Employee Order by Salary DESC, EmployeeID DESC

 

4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000

 

7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000

 

Dynamic Order By


It's possible to make the column specified in ORDER BY dynamic based on other parameters.

The first example will sort on salary based on the value of @OrderType...

DECLARE @OrderType Varchar(32)
Set @OrderType = 'Salary'

-- Simple Dynamic Order By
Select * From Employee
	ORDER BY
		CASE WHEN @OrderType = 'Salary' THEN Salary ELSE DateOfHire END

 

3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000

 

The second will sort on date with the same sql; only the variable has changed:


DECLARE @OrderType Varchar(32)
Set @OrderType = 'Date'

-- Simple Dynamic Order By
Select * From Employee
	ORDER BY
		CASE WHEN @OrderType = 'Salary' THEN Salary ELSE DateOfHire END

 

5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000

 

Unfortunately, this has significant limitations - you can only specify one column, and you can't use the DESC keyword.

 
-- This doesn't work
DECLARE @OrderType Varchar(32)
Set @OrderType = 'Salary'

Select * From Employee
	ORDER BY
		CASE WHEN @OrderType = 'Salary' THEN Salary, DateOfHire ELSE DateOfHire, Salary END
 
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
 

DECLARE @OrderType Varchar(32)
Set @OrderType = 'Salary'

-- This doesn't work either
Select * From Employee
	ORDER BY
		CASE WHEN @OrderType = 'Salary' THEN Salary DESC ELSE DateOfHire END

 
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'DESC'.
 

Fortunately, the issue can be circumvented fairly easily using a few extra keywords - RANK() and OVER:


DECLARE @OrderType Varchar(32)
Set @OrderType = 'Salary'

-- Dynamic Order By with Multiple columns in ORDER BY using RANK() OVER
Select * From Employee
	ORDER BY
		CASE WHEN @OrderType = 'Salary' THEN RANK() OVER (ORDER BY Salary DESC, DateOfHire ASC)
			ELSE RANK() OVER (ORDER BY DateOfHire ASC, Salary DESC)
		END

 

7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000

 

Aliasing


Columns in ORDER BY can be referenced using a numerical alias.  This alias corresponds to the column's position in the select list (not in the structure of the table).  This can be a convenient shorthand, but has limited overall usefulness.


-- Columns can be aliased using a number
-- Orders by Salary
Select * from Employee Order by 3 DESC

 

4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000

 

-- Orders by EmployeeName because it's now the third column in the select list
Select EmployeeID, * from Employee Order by 3 DESC

 

7	7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
2	2	T.O.			500000.00	2003-01-01 00:00:00.000
1	1	Steve Smith		100000.00	2001-01-01 00:00:00.000
4	4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
3	3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
6	6	Donald Driver	232345.00	1999-01-01 00:00:00.000
5	5	Brett Favre		232345.00	1975-01-01 00:00:00.000

 

Forcing rows to the top or bottom of the result set


If there's a need for certain items to be at the top or bottom of the result set, this can be accomplished by assigning a weight to a row.  The weight is then used as the first item in the ORDER BY clause.

In this example, we want T.O. to appear last - so we'll assign a weight of 1 to him and 0 to everyone else:

-- Force a value to be at the bottom or top
-- Make T.O. Last regardless of Salary
SELECT *
FROM Employee
ORDER BY (case WHEN EmployeeName = 'T.O.' THEN 1 ELSE 0 END), Salary DESC
 

4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000

 

Assigning a weight of -1 to Donovan, then 0 to everyone else will cause him to appear first in the list regardless of the salary.

-- Make Donovan first
SELECT *
FROM Employee
ORDER BY (case WHEN EmployeeName = 'Donovan McNabb' THEN -1 ELSE 0 END), Salary DESC
 

3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000

 

Multiple columns can be weighted:


SELECT *
FROM Employee
ORDER BY (case WHEN EmployeeName = 'Donovan McNabb' THEN -1 
			   WHEN EmployeeName = 'Tom Brady' THEN -2
				ELSE 0 END), Salary DESC

 

7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000

 

This functionality is often used to move NULLs to the end of the result set:

 Insert Employee VALUES (8, NULL, NULL, GetDate())
 Select * from Employee Order by Salary 

8	NULL			NULL		2010-09-20 17:22:31.833
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000

 

By default, NULL values are listed first in an ordered set. They can be shifted to the bottom using this technique.


SELECT *
FROM Employee
ORDER BY (case WHEN EmployeeName IS NULL THEN 1 ELSE 0 END), Salary

 

3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
8	NULL			NULL		2010-09-20 17:22:31.833

 

Behavior in views and functions


On MSDN's ORDER BY article, there's a note at the top:

NoteNote

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Topic link iconWhat does this mean?  Basically, using ORDER BY in a view or udf definition doesn't guarantee the ordering of the results returned from the view, as demonstrated here.  The ORDER BY clause is included in the view definition...


--Include ORDER BY SALARY DESC in view definition:
CREATE VIEW Vw_OrderBy AS Select TOP 100 PERCENT * from Employee Order by Salary DESC

 
-- View is not ordered:
Select * from Vw_OrderBy

 

1	Steve Smith		100000.00	2001-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000
8	NULL			NULL		2010-09-20 17:22:31.833

 

... but the order by clause needs to be included in the select statement to order the result set.


-- Order by needs to be specified in the Select statement.
Select * from Vw_OrderBy Order by Salary

 

8	NULL			NULL		2010-09-20 17:22:31.833
3	Donovan McNabb	8000.00		2007-01-01 00:00:00.000
1	Steve Smith		100000.00	2001-01-01 00:00:00.000
5	Brett Favre		232345.00	1975-01-01 00:00:00.000
6	Donald Driver	232345.00	1999-01-01 00:00:00.000
2	T.O.			500000.00	2003-01-01 00:00:00.000
4	Peyton Manning	1000000.00	2008-01-01 00:00:00.000
7	Tom Brady		1000000.00	2007-01-01 00:00:00.000