tommyh (9/6/2010)
Hugo Kornelis (9/6/2010)
Good question!In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:
Because Microsoft havent implemented it.
No, because Microsoft has chosen not to go against the standard.
From ISO/IEC 9075-2:2003 (E), 7.9 <group by clause>:
"Syntax Rules
1) Each <grouping column reference> shall unambiguously reference a column of the table resulting from
the <from clause>. A column referenced in a <group by clause> is a grouping column."
SQL Server does allow you to use expressions in the GROUP BY instead of only simple column references (which extends the standard but oes not violate it). Allowing column aliases would be a violation of the standard, and would break compatibility with ANSI-standard conformant products and code.
We are allowed to use aliases in Group by (and other stuff) in the form of table aliases. Therefore there is no real reason for not allowing column aliases as well (well maybe a bit of performance).
Of course we can use a table alias in the GROUP BY. That follows logically from the same logical query evaluation order described above. The FROM clause is evaluated first. Table aliases are assigned in the FROM clause. As a result, you can use them in alll other clauses (and, in fact, MUST use them - once you assign a table alias, you can no longer reference the table by its original table name).