4 ms·
It's especially egregious in the ORDER BY, since there you have the option of using column aliases.
by wfriesen 7y ago
It's especially egregious in the ORDER BY, since there you have the option of using column aliases.
- tempguy9999 7y agoI always, always forgot what column aliases I can use where. Thanks for the reminder.
- commandlinefan 7y agoAre you saying you can't use column aliases in group by? What version of Postgres are you using? I just tried it in 11.5 and it worked: # select cust_id as c, sum(avail_balance) as b from account group by c order by b;
- tempguy9999 7y agoInteresting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.
- commandlinefan 7y agoHuh - I guess I never thought about it. It makes sense to disallow it, though - column aliases are there to rename complex expressions, which you probably _shouldn't_ be grouping on anyway.
- tempguy9999 7y agoI think it's for other reasons (and grouping on expressions is quite reasonable anyway). It's (IIRC!) something to do with the situation of select x + y as x from ... group by x which x are we talking about? (Logically that example is crap because only the alias x makes sense, but something like that anyway).
- yellowapple 7y ago> which you probably _shouldn't_ be grouping on anyway. This is frequently unavoidable, though. Or more precisely: it could be avoided with a sane database design, but the databases on which I have to work for my day job are the precise opposite of "well-designed", so grouping on complex expressions is unfortunately an inevitability.
- commandlinefan 7y agoThat's true - I can definitely imagine having to group on something like "concat(lastname + ', ' + firstname)".
- wfriesen 7y agoI was thinking of Oracle, where aliases are evaluated at column protection time, so after grouping but before ordering.