Lew <noone@xxxxxxxxxxxxxxxxxxxxxxx> writes: > Eric B. Ridge wrote: >> That explains it. Thanks. Breaks the rule of least surprise, but it is SQL. > I don't think it does break the rule of least surprise. How would one > expect the column or the alias to have precedence without knowledge of > the rule from documentation? The only thing I would expect if I were > unaware of the rule is that there might be a conflict and that I'd > have to look up the precedence. There's actually a good deal more there than meets the eye. Our SELECT reference page spells it out, if you read the fine print. In particular it's worth noting this bit in the Compatibility section: Namespace Available to GROUP BY and ORDER BY In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names. SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
