Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>