Wednesday, March 21, 2012

SELECT column alias can be used in ORDER BY but not in GROUP BY clause

We can use SELECT column alias in ORDER BY clause. But we can't use in GROUP BY clause.

SQL is implemented as if a query was executed in the following order:
  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
This order in most relational database systems explains which names (columns or aliases) are valid because they must have been introduced in a previous step. There are exceptions though.
You could always use a subquery so you can use the alias 
SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use the alias in the ORDER BY clause. 
In Short,
ORDER BY clause is processed after the SELECT statement; 
GROUP BY clause is processed before the SELECT statement.

No comments:

Post a Comment