[
https://issues.apache.org/jira/browse/PHOENIX-1768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377103#comment-14377103
]
Julian Hyde edited comment on PHOENIX-1768 at 3/24/15 2:04 AM:
---------------------------------------------------------------
Microsoft SQL Server had a nice SQL extension that allowed you to apply "AS" to
any expression, not just top-level column expressions. Thus I think you could
write
{code}SELECT ((x + y) AS xy) * 2 AS b, xy * 3 AS c
FROM t
WHERE xy < 4
GROUP BY b{code}
Note that "xy" is defined inside the expression column "b", and used in two
other places, in "c" and in the WHERE clause.
But it's non-standard, so don't expect to see it in Phoenix (or Calcite)
anytime soon. :)
was (Author: julianhyde):
Microsoft SQL Server had a nice SQL extension that allowed you to apply "AS" to
any expression, not just top-level column expressions. Thus I think you could
write
{code}SELECT ((x + y) AS xy) * 2 AS b, xy * 3 AS c
FROM t
WHERE xy < 4
GROUP BY b{code}
But non-standard, so don't expect to see it in Phoenix (or Calcite) anytime
soon. :)
> 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)
