[
https://issues.apache.org/jira/browse/PHOENIX-1768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377085#comment-14377085
]
Julian Hyde commented on PHOENIX-1768:
--------------------------------------
An "ordinary grouping set" is typically "grouping column reference" (or a list
thereof) which is typically "column reference". That is, a reference to a
column in one of the tables in the FROM clause, e.g. "emp.deptno".
The standard does not even allow an expression, such as "GROUP BY emp.deptno +
1".
Most databases go beyond that, and do allow expressions, so you can simply
paste the expression from the SELECT clause into the GROUP BY clause.
The ORDER BY syntax explicitly allows expressions, e.g. "ORDER BY emp.deptno -
emp.empno". The part of the standard you have pasted does not allow ORDER BY
ordinal; I recall differently, that it does allow ordinals, but maybe I am
mistaken, and even ORDER BY ordinal is an extension to the standard.
In summary: no way does the standard endorse "GROUP BY ordinal".
> GROUP BY should support column position as well as column alias
> ---------------------------------------------------------------
>
> Key: PHOENIX-1768
> URL: https://issues.apache.org/jira/browse/PHOENIX-1768
> Project: Phoenix
> Issue Type: Bug
> Reporter: Serhiy Bilousov
>
> In postgreSQL (and many others DBs) you can specify not only column name for
> the GROUP BY but column number (position in SELECT part) as well as column
> alias.
> see:
> http://www.postgresql.org/docs/9.4/static/queries-order.html
> http://www.postgresql.org/docs/9.4/static/sql-select.html#SQL-GROUPBY
> Adding such support would be very helpful and sometimes necessary.
> I can provide real queries example if required but basically we want
> something like this
> given query
> SELECT a, b, TRUNC(current_date(),'HOUR') AS date_truncated FROM table
> we want
> GROUP BY 1, 2
> Having just column number would cover both but having column alias would make
> queries more readable and human friendly. Plus make it one little stem closer
> to postgreSQL and SQL standard.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
