Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

ORDER BY the numbers

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...