StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy can't I GROUP BY 1 when it's OK to ORDER BY 1?
text
Body
copied!<p>Why are column ordinals legal for <code>ORDER BY</code> but not for <code>GROUP BY</code>? That is, can anyone tell me why this query</p> <pre><code>SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID </code></pre> <p>cannot be written as </p> <pre><code>SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1 </code></pre> <p>When it's perfectly legal to write a query like</p> <pre><code>SELECT OrgUnitID FROM Employee AS e ORDER BY 1 </code></pre> <p>?</p> <p>I'm really wondering if there's something subtle about the relational calculus, or something, that would prevent the grouping from working right.</p> <p>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:</p> <pre><code>SELECT DATEPART(YEAR,LastSeenOn), COUNT(*) FROM Employee AS e GROUP BY DATEPART(YEAR,LastSeenOn) </code></pre> <p>I would think that SQL's rule of normalize to only represent <em>data</em> once in the database ought to extend to code as well. I'd want to only right that calculation expression once (in the <code>SELECT</code> column list), and be able to refer to it by ordinal in the <code>GROUP BY</code>.</p> <p>Clarification: I'm specifically working on SQL Server 2008, but I wonder about an overall answer nonetheless.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload