Have you ever needed to order by a calculated column? You might have written it something like this:

SELECT LoginID, YEAR(HireDate) AS HireYear, 
MONTH(HireDate) AS HireMonth
FROM HumanResources.Employee
ORDER BY YEAR(HireDate), MONTH(HireDate),
LoginID

Did you know you don’t have to put the calculations in the ORDER BY? You don’t even have to put column names.

SELECT LoginID, YEAR(HireDate) AS HireYear, 
MONTH(HireDate) AS HireMonth
FROM HumanResources.Employee
ORDER BY 2, 3, 1

Each number represents a position in the field list. 1=’LoginID’, 2=’YEAR(HireDate)’ etc.

BOL says

“A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function.”

When I copied this out I noticed you can’t use an integer with the ORDER BY in a ranking function, which is rather a shame. Even so, if you are sorting on calculated columns using integers in your order by can make life a whole lot easier.

Also I should note that when I check execution plans and timing using the integer vs typing the column/calculation out there is absolutely no difference.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL