[
https://issues.apache.org/jira/browse/CALCITE-5347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5347:
---------------------------------
Description:
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:java}
SELECT a BY b
FROM t {code}
as syntactic sugar for
{code:java}
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{}}}.
This feature was inspired by [Shakti|https://shakti.com/], a SQL-like language
by Arthur Whitney, author of the k language.
was:
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 that
{code:java}
SELECT a BY b
FROM t {code}
is syntactic sugar for
{code:java}
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{}}}.
This feature was inspired by [Shakti|https://shakti.com/], a SQL-like language
by Arthur Whitney, author of the k language.
> Add 'SELECT ... BY', a syntax extension to eliminate duplicates and sort
> ------------------------------------------------------------------------
>
> 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:java}
> SELECT a BY b
> FROM t {code}
> as syntactic sugar for
> {code:java}
> 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{}}}.
> 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)
