Forum Stats

  • 3,824,844 Users
  • 2,260,430 Discussions
  • 7,896,328 Comments

Discussions

WITHIN GROUP for FETCH clause

Thorsten Kettner
Thorsten Kettner Member Posts: 42 Red Ribbon
edited May 30, 2020 5:51AM in Database Ideas - Ideas

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, salFROM empORDER BY enameFETCH 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, deptnoFROM empORDER BY sal, enameFETCH FIRST ROWS WITH TIES WITHIN GROUP (ORDER BY DENSE_RANK OVER (PARTITION BY deptno ORDER BY sal DESC));
Thorsten KettnerSven W.berx
3 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jul 1, 2020 7:05AM

    Have to say your first example does not make sense to me. Maybe care to show the data how you expect it to be returned?

    The second example however looks very useable.

    Since fetch first is implemented as the analytic row_number function, this should not be to difficult to implement.
    However the order by usually determines the order, this would then be undermined by the additional order by clause in the window.
    There might be some ambiquity that needs to be resolved.

    As for the syntax, I don't think that WITHIN GROUP is needed, since we don't have groups (as we have in the aggregate version of listagg.
    Partition by would define the groups nicely. I would suggest OVER to specifiy the start of the analytic window clause.

    SELECT sal, ename, deptno  FROM emp  ORDER BY sal, ename  FETCH FIRST ROWS WITH TIES OVER (PARTITION BY deptno ORDER BY sal DESC);

     

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Jul 2, 2020 2:13PM

    Ok I got it now. Assuming 3 employees have the same highest salary.
    This order by returns the correct ordering:elect

    SELECT sal, ename FROM emp  ORDER BY sal desc, ename;

    Example data

    1000     King    

    1000     Smith

    1000     Alan

    900       Terry

    870       Ravenous Bugblatter Beast of Traal

    The problem is that currently the fetch first clause does not allow to stop after all employees with the same highest salary are returned. In this case after line 3.

    The WITH TIES part already indicates that we expect duplicates. So maybe a simpler syntax is possible.

    ... FETCH FIRST ROWS WITH TIES OVER (sal);

    or maybe even without the with ties keyword.

    ... FETCH FIRST ROWS OVER (order by sal DESC);
    berx