Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

WITHIN GROUP for FETCH clause

Thorsten KettnerMay 30 2020

I suggest to introduce a WITHIN GROUP for the FETCH clause, because we may want an ORDER BY clause that is different from the query's ORDER BY clause - just like in LISTAGG where we know WITHIN GROUP from.

Example: Get the employees with the highest salary ordered by name:

SELECT ename, sal

FROM emp

ORDER BY ename

FETCH FIRST ROWS WITH TIES WITHIN GROUP (ORDER BY sal DESC);

Here is another example: Get the employees that have the highest salary per department and order them by salary and name.

SELECT sal, ename, deptno

FROM emp

ORDER BY sal, ename

FETCH FIRST ROWS WITH TIES WITHIN GROUP (ORDER BY DENSE_RANK OVER (PARTITION BY deptno ORDER BY sal DESC));

Comments

Post Details

Added on May 30 2020
2 comments
595 views