CODEWITHSUNDEEP
sql-server
jitendra
jitendra

Reputation: 577

Group by working in SQL Server 2000 but not in SQL Server 2005

I used following query in SQL Server 2000

SELECT 
   U.FirstName,
   SUM(VE.Score)AS Score, SUM(VE.QuizTime) AS Time, 
   SUM(VE.IsQuizType) AS QuizesAttempted,
   SUM(VE.IsProgrammingType) AS ProgrammingProblemsAttempted 
from 
    Users U INNER JOIN VirtualExercise VE on U.UserID=VE.UserID 
where U.UserID IN( 10,11 ) AND ProgramID = 2 
group by U.FirstName 
order by VE.Score desc

It working fine in SQL Server 2000 but not working in SQL Server 2005. Gives following error:

Column "VirtualExercise.Score" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. --- Inner Exception

Please help...

Upvotes: 1

Views: 956

Answers (3)

gbn
gbn

Reputation: 432210

SQL Server 2005 is correct: you are trying to order by a value that doesn't exist in the output because you must either GROUP on it or aggregate it. VE.Score is neither. SQL Server 2000 allowed some ambiguities like this (edit: see "ORDER BY clause" at http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx for info on this)

I assume you mean

ORDER BY SUM(VE.Score) DESC

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

SQL Server 2000 is kind of dumb where it comes to table and column aliases in the result set. Provided an explicit alias column is introduced (e.g. Score in your case, Col3 in the example below), it doesn't matter what table alias you use in the ORDER BY clause:

create table #T1 (
    ID int not null primary key,
    Col1 varchar(10) not null
)
go
create table #T2 (
    ID int not null primary key,
    Col2 varchar(10) not null
)
go
insert into #T1 (ID,Col1)
select 1,'abc' union all
select 2,'def'
go
insert into #T2 (ID,Col2)
select 1,'zyx' union all
select 2,'wvu'
go
select *,1 as Col3 from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID order by t2.Col3

Since you presumably want to sort by the computed Score column, just remove the VE. prefix.

Upvotes: 2

Shreyas
Shreyas

Reputation: 26

Since you have defined SUM(VE.Score)AS Score in select , do you mean ORDER BY Score DESC

Upvotes: 0

Related Questions