CODEWITHSUNDEEP
sql-server-2000aggregate-functions
Ernst
Ernst

Reputation: 103

Column in aggregate function but sqlserver says it isn't

I tried to run the following query:

select a,b,min(c) from tablename where e=1 and f=1 group by a,b order by a,b,c

But got the error:

Column name 'tablename.c' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

However c is contained in the aggregate function min(), so what's the issue here? Is there a workaround?

This is on sqlserver 2000 (using the sqlserver 2008 management console).

Upvotes: 1

Views: 566

Answers (3)

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30875

As you are using the aggregation on that column, is no longer c but "aggregated c" that is why you can not use it in the order by clause.

You need to alias that column to use it in order by 

select a,b,min(c) as min_c from tablename where e=1 and f=1 group by a,b order by a,b,min_c

EDIT:

Why the column c is not available ?

While in the statement we use clause group by, our original table i transformed into new temporary one.

In your case you used the column a and b to group, the the data in those column will not be changed only limited to unique.

On the column c you are using a function that for each unique group will retrieve the lowest value of c, for this purpose a new column has to be created that will store that results.

The order by clause is the last part of query that is executed over the select section. So over the result of the temporary table not the source one.

The simply order of statement execution goes like this: 

FORM 
 JOIN
WHERE
GROUP BY 
HAVING 
SELECT 
ORDER BY

Upvotes: 3

Beno
Beno

Reputation: 4753

try this:

select a,b,min(c) 
from tablename 
where e=1 and f=1 
group by a,b 
order by a,b,min(c)

or this:

select a,b,min(c) 
from tablename 
where e=1 and f=1 
group by a,b
order by 1,2,3

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176906

try this 

select * from
(
  select a,b,min(c) as c from tablename where e=1 and f=1 group by a,b 
) d order by a,b,c

Upvotes: 0

Related Questions