3 ms·
The GROUP BY section is odd: > You can use as to name scalar values anywhere they appear. Except in a group by. -- can't name this value > select x2 from
by Izkata 5y ago
The GROUP BY section is odd:
> You can use as to name scalar values anywhere they appear. Except in a group by.
-- can't name this value
> select x2 from foo group by x+1 as x2;
ERROR: syntax error at or near "as"
LINE 1: select x2 from foo group by x+1 as x2;
-- sprinkle some more select on it
> select x2 from (select x+1 as x2 from foo) group by x2;
?column?
----------
(0 rows)
Looking at that first one I'm just kinda like "well duh, there's nothing special there" - it doesn't work with ORDER BY either, you use that to rename columns (on SELECT) or tables (on FROM and JOIN).
And then it goes on to show ways to work around that:
> Rather than fix this bizaare oversight, the SQL spec allows a novel form of variable naming - you can refer to a column by using an expression which produces the same parse tree as the one that produced the column.
Instead of just... using the renamed column?
select x+1 as x2 from foo group by x2;