Oracle: Simulate Lead Analytical Function (Manual Lead)

ORIGINAL QUERY

select
    deptno, empno, ename,
    lead(ename) over(partition by deptno order by ename) lead_emp
from scott.emp
order by deptno, ename

SIMULATED QUERY
select deptno, empno, ename,
(
    select ename from
    (
        select ename, deptno, rownum rn from
        (select empno, ename, deptno from scott.emp order by deptno, ename)
    ) a where a.rn = emp.rn + 1 and a.deptno=emp.deptno
) lead_emp
from (select empno, ename, deptno, rownum rn from (select * from scott.emp order by deptno, ename)) emp

OUTPUT
 






















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



3 comments:

  1. I wasn't aware that simulate lead analytic function is that easy to implement. Whenever you explain any topic it becomes so easy to understand. I easily understand simulated form of query. Thanks for the post.

    ReplyDelete
  2. This particular is usually apparently essential and moreover outstanding truth along with for sure fair-minded and moreover admittedly useful My business is looking to find in advance designed for this specific useful stuffs… cotton percale sheets on sale , cheap designer comforter sets

    ReplyDelete
  3. A round of applause for your mind blowing article. Much thanks to you, Fantastic.

    ReplyDelete