Why can't I GROUP BY 1 when it's OK to ORDER BY 1?

user_10082 0
Viewed 8328

Why are column ordinals legal for ORDER BY but not for GROUP BY? That is, can anyone tell me why this query

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID

cannot be written as 

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1

When it's perfectly legal to write a query like

SELECT OrgUnitID FROM Employee AS e ORDER BY 1

?

I'm really wondering if there's something subtle about the relational calculus, or something, that would prevent the grouping from working right.

The thing is, my example is pretty trivial. It's common that the column that I want to group by is actually a calculation, and having to repeat the exact same calculation in the GROUP BY is (a) annoying and (b) makes errors during maintenance much more likely. Here's a simple example:

SELECT DATEPART(YEAR,LastSeenOn), COUNT(*)
    FROM Employee AS e
    GROUP BY DATEPART(YEAR,LastSeenOn)

I would think that SQL's rule of normalize to only represent data once in the database ought to extend to code as well. I'd want to only right that calculation expression once (in the SELECT column list), and be able to refer to it by ordinal in the GROUP BY.

Clarification: I'm specifically working on SQL Server 2008, but I wonder about an overall answer nonetheless.

sql
5 Answers

databases that don't support this basically are choosing not to. understand the order of the processing of the various steps, but it is very easy (as many databases have shown) to parse the sql, understand it, and apply the translation for you. Where its really a pain is when a column is a long case statement. having to repeat that in the group by clause is super annoying. yes, you can do the nested query work around as someone demonstrated above, but at this point it is just lack of care about your users to not support group by column numbers.

user_191934090
Related