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
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
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.
ReplyDeleteThis 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
ReplyDeleteA round of applause for your mind blowing article. Much thanks to you, Fantastic.
ReplyDelete