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

Wang Yanlin commented on CALCITE-3402:
--------------------------------------

I think you are right about "SQL03 7.10 Rule 11a".

The code in *SqlWindow* follows the stand, but I cannot see why the standard do 
not allow range with compoud order by.

> Allow RANGE with compoud ORDER BY clause
> ----------------------------------------
>
>                 Key: CALCITE-3402
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3402
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.18.0, 1.19.0
>            Reporter: benj
>            Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause 
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN 
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c 
>       UNION SELECT 2, 'c', 4 
>       UNION SELECT 1, 'c', 4
>       /* UNION ... */
>      ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE 
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value  with an 
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR 
> will not be compatible and should be rewrite, and for some other function 
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE  is possible with some SGBDR like 
> Postgres: 
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Reply via email to