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

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.

Column alias in order by and where

If you ever wonder why column alias can be used in order by and not in where clause, it’s the logical query processing order. Pinal Dave blogged about the complete order here.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
WHERE g = 'Executive General and Administration'

In the above query, from clause is executed first followed by where clause, and select goes last. So where clause is trying to use a column g which doesn’t exist until select completes execution and thus resulting in a “invalid column g” error.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
ORDER BY g

But order by in this above query is executed at the end after select has created a column g.

And order by lived happily ever after >:]

~Sam.


Comments

Posted by Anonymous on 9 November 2011

Pingback from  Dew Drop – November 9, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.