[ https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jiajun Xie updated CALCITE-6240: -------------------------------- Priority: Minor (was: Major) > Remove ROW_NUMBER if OVER PARTITION BY unique key > ------------------------------------------------- > > Key: CALCITE-6240 > URL: https://issues.apache.org/jira/browse/CALCITE-6240 > Project: Calcite > Issue Type: Improvement > Components: core > Reporter: Jiajun Xie > Assignee: Jiajun Xie > Priority: Minor > > The SQL Only have ROW_NUMBER function. > {code:java} > SELECT empno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename > FROM emp > ) WHERE rn <= 5{code} > to > {code:java} > SELECT empno, ename FROM emp{code} > > If we know every partition count is less than filter count, we can optimize > {code:java} > SELECT deptno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename > FROM emp > ) WHERE rn <= N > -- Statistic let we know every department count is less than N{code} > to > {code:java} > SELECT empno, ename FROM emp {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)