Approx_rank, Approx_sum, Approx_count ( Oracle 18C New Functions)

1. "GROUP BY" clause is mandatory to use approx_* functions.
2. Approx_count & Approx_sum functions has to be used with "HAVING" clause containing approx_rank function.
3. Both approx_* functions and non approx_* aggregate functions are not allowed in same select statement.
4. "ORDER BY" clause within approx_rank function should use only approx_* functions only (+ with "DESC" keyword only).
5. "HAVING" clause should contain "<= number" or "< number" conditions only. Follow this format only ('APPROX_RANK(PARTITION BY ... ORDER BY ... DESC) <(=) n')

SELECT   empno,          approx_rank ( order by approx_sum(sal) DESC ) FROM     scott.emp GROUP BY empno HAVING   approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10 ORDER BY 2;

SELECT   deptno,          Approx_sum(sal),          approx_rank ( order by approx_sum(sal) DESC ) FROM     scott.emp GROUP BY deptno HAVING   approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10;

SELECT   deptno,          Approx_count(empno),          approx_rank ( order by approx_count(empno) DESC ) FROM     scott.emp GROUP BY deptno HAVING   approx_rank ( ORDER BY approx_count(empno) DESC ) <=10;