[jira] [Commented] (CALCITE-5347) Add 'SELECT ... BY', a syntax extension that is shorthand for GROUP BY and ORDER BY

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17624126#comment-17624126 ] 

Julian Hyde commented on CALCITE-5347:
--------------------------------------

On occasion, you may wish to sort by an aggregate function. For example, to write the following query to find sort departments by their salary budget:

{code:sql}
SELECT deptno, SUM(sal) AS sum_sal
FROM Emp
GROUP BY deptno
ORDER BY sum_sal DESC
{code}

I can't think of a way to support that pattern using the {{SELECT ... BY}} syntax that wouldn't be confusing/arbitrary.

> Add 'SELECT ... BY', a syntax extension that is shorthand for GROUP BY and ORDER BY
> -----------------------------------------------------------------------------------
>
>                 Key: CALCITE-5347
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5347
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>
> Many people have observed that SQL query syntax is verbose if you want to eliminate duplicates and sort. Business intelligence tools often have query languages that eliminate duplicates and sort by default; we cannot do that in the SQL {{SELECT}} expression, because that would break compatibility. But we propose the following variation of {{SELECT}} that is distinguishable from regular {{SELECT}} (i.e. queries that use it would be invalid in ordinary SQL) but concise and intuitive.
> We propose
> {code:sql}
> SELECT a BY b
> FROM t {code}
> as syntactic sugar for
> {code:sql}
> SELECT b, a
> FROM t
> GROUP BY b
> ORDER BY b{code}
> In the above, {{a}} and {{b}} may be lists of columns, the columns may include aliases, and the columns in {{b}} may include sort-key modifiers such as {{DESC}} and {{NULLS LAST}}.
> For example,
> {code:sql}
> SELECT e.ename, e.empno BY d.dname AS dept DESC, e.job AS title
> FROM Emp AS e
>   JOIN Dept AS d ON e.deptno = d.deptno
> WHERE d.loc = 'CHICAGO'{code}
> is shorthand for
> {code:sql}
> SELECT d.dname AS dept, e.job AS title,
>   e.ename, e.empno
> FROM Emp AS e
>   JOIN Dept AS d ON e.deptno = d.deptno
> WHERE d.loc = 'CHICAGO'
> GROUP BY d.dname, e.job
> ORDER BY d.dname DESC, e.job {code}
>  
> This feature was inspired by [Shakti|https://shakti.com/], a SQL-like language by Arthur Whitney, author of the k language.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)