[ https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16952633#comment-16952633 ]
benj commented on CALCITE-3402: ------------------------------- With Drill 1.16 and 1.17 (from 2019-10-14) ROWS is not either possible. {code:sql} Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not supported See Apache Drill JIRA: DRILL-3188 {code} More precisely with ORDER BY, the only possibilities to write ROWS clause are (the 2 possibilities are identical) : * ROWS BETWEEN UNBOUNDED PRECEDING AND current row * ROWS UNBOUNDED PRECEDING >>If you use {{RANGE}} with a composite {{ORDER BY}}, does PostgreSQL apply the >>range to just the first key? The documentation you referenced does not say. No Postgres apply all the key and then apply the range on {noformat} The ORDER BY option determines the order in which the rows of a partition are processed by the window function. It works similarly to a query-level ORDER BY clause, but likewise cannot use output-column names or numbers. Without ORDER BY, rows are processed in an unspecified order.{noformat} in practice, with a table _tex_ {noformat} a,b,c 1,1,1 2,2,1 3,2,2 4,3,1 5,3,2 6,3,3{noformat} you can do {code:sql} -- Postgres SELECT * , last_value(a) OVER(ORDER BY b RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) sort_b , last_value(a) OVER(ORDER BY b, c DESC RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) sort_b_and_c FROM tex WHERE a = 4 -- => 4;3;1;6;4 -- The result of columns 4 and 5 well depends of all compound of the ORDER BY -- !Postgres {code} toto > 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)