Group by difference between SQL 2005 and 2000?

[quote user="amu_27"]
I came across situation where I want to make sure this is known.
in SQL 2000 in group by clause I have to put the calculate field as its defined in Select to make it work i.e.
select a.1, a.2 * (a.3 - a.4)
from a
group by a.1, a.2 * (a.3 - a.4)
rather in SQL 2005 it is not necessary and just need the partial as well for grouping it
select a.1, a.2 * (a.3 - a.4)
from a
group by a.1, a.3 - a.4
now SQL 2005 select fails in SQL 2000. Is this new feature of SQL 2005?
[/quote]
I can not reproduce your issue. Can you post a repro script?

Your statement *SHOULD* fail on both versions as this is a grouping statement and columns in the SELECT list must also appear in the GROUP BY clause.

Frank
This works in 2005 and fails in 2000
select a1, 2 * (a3 - a4)
from
(
select 23 as a1, 56 as a2,8 as a3,6 as a4
) as a
group by a1, a3 - a4
I think in SQL Server a3-a4 becomes an expression or a literal value rather than a new column

[quote user="Madhivanan"]
Frank
This works in 2005 and fails in 2000
select a1, 2 * (a3 - a4)
from
(
select 23 as a1, 56 as a2,8 as a3,6 as a4
) as a
group by a1, a3 - a4
I think in SQL Server a3-a4 becomes an expression or a literal value rather than a new column
[/quote]
Madhi,
you're multiplying (a3-a4) by the constant 2. This will work. However if you change your expression 2 * (a3 - a4) to a2 * (a3 - a4) it will fail. []

Good point Frank []