SELECT list ‘alias’ in the WHERE or GROUP BY clause

While executing the SELECT queries in SQL server, many times we need the desired output column aliases and for that we end up writing the TSQL queries like this:

SELECT Col1 AS MyColumn FROM Table1 

This works fine also for any number of columns. But at the same time we may require to put some conditions on these columns by using WHERE or GROUP BY clauses like:

SELECT Col1 AS MyColumn FROM Table1 

WHERE MyColumn = ‘XYZ‘     –Will not Work


SELECT Col1 AS MyColumn FROM Table1 

GROUP BY MyColumn           –Will not Work

: But this will not work in SQL server, because SELECT is interpreted & Evaluated at last. It means at the time WHERE & GROUP BY are applied, at that time MyColumn does not make any sense to WHERE or GROUP BY. So we have to use the original column names instead of Aliases in WHERE , GROUP BY or HAVING clauses to enforce some conditions on those columns.

There is one exception to this evaluation of the aliases. That is with the ORDER BY clause. ORDER BY is not the TSQL but rather a Cursor operator. ORDER BY is applied to the data after when the complete data has been received by the Cursor after SELECT statements.

So in case of ORDER BY we can use Aliases marked in SELECT statements like:

SELECT Col1 AS MyColumn FROM Table1

ORDER BY MyColumn           –-Will Work Fine

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s