For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
SQL> select * from testing; EMPNO ENAME SAL ---------- -------------------- ---------- 2 bas 2000 1 sun 3000 3 ben 4000 3 rows selected. SQL> select * from testing order by empno; EMPNO ENAME SAL ---------- -------------------- ---------- 1 sun 3000 2 bas 2000 3 ben 4000 3 rows selected.
order by empno,sal;
select nth_value(empno,rownum) over(order by empno rows between unbounded preceding and unbounded following), nth_value(sal, rownum) over(order by sal rows between unbounded preceding and unbounded following) from emp;
SELECT A.empno, B.sal FROM (SELECT rownum r1, empno from (SELECT empno FROM emp ORDER BY empno)) A, (SELECT rownum r2, sal FROM (SELECT sal FROM emp ORDER BY sal)) B WHERE A.r1 = B.r2
SQL> select rownum, nth_value(empno,rownum) over(order by empno rows between unbounded preceding and unbounded following) empno, nth_value(sal, rownum) over(order by sal rows between unbounded preceding and unbounded following) sal from emp order by rownum / ROWNUM EMPNO SAL ---------- ---------- ---------- 1 7369 800 2 7369 800 3 7369 800 4 7369 800 5 7369 800 6 7369 800 7 7369 800 8 7369 800 9 7369 800 10 7369 800 11 7369 800 12 7369 800 13 7369 800 14 7369 800 14 rows selected.