June 17, 2013 by Kenneth Fisher
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.
“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.