Oracle: Simulating Rank Analytical Functions (Manual Rank)

ORIGINAL QUERY
select
    empno,
    ename,
    deptno,
    sal,
    rank() over(partition by deptno order by sal) rnk
from
    scott.emp

--------------------------------------------------------
SIMULATED QUERY
select
    empno,
    ename,
    deptno,
    sal,
    (select count(sal)+1 from scott.emp b where b.deptno=a.deptno and b.sal < a.sal) rnk
from
    scott.emp a
order by
    deptno, sal

OUTPUT



Related Links:
- Oracle: Simulate Lead Analytical Function (Manual Lead)
- Oracle: Simulating Dense_Rank Analytical Functions (Manual Dense_Rank)
- Oracle: Getting Simulated Cumulative Sum (Running Total)
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- FIRST_VALUE and LAST_VALUE with Windowing Clause

No comments:

Post a Comment