Hacker News Reader

Comment by wfriesen | Hacker News Reader

It's especially egregious in the ORDER BY, since there you have the option of using column aliases.

Are 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;

Interesting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.

Huh - 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.

I 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).

> 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.

That's true - I can definitely imagine having to group on something like "concat(lastname + ', ' + firstname)".

I was thinking of Oracle, where aliases are evaluated at column protection time, so after grouping but before ordering.

I always, always forgot what column aliases I can use where. Thanks for the reminder.

Reply on news.ycombinator.com