Skip to Main Content

SQL & PL/SQL

Announcement

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.

strange result of nth_value of 11gR2.

Aketi JyuuzouAug 18 2010 — edited Aug 21 2013
http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions112.htm
<i>n determines the nth row for which the measure value is to be returned. n can be a constant,
bind variable, column, or an expression involving them</i>

Why below SQL returns same result ?:|
select * from v$version;

BANNER
-------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

with t(empno,sal,rn) as(
select 101, 720,1 from dual union
select 102, 850,2 from dual union
select 103,1250,3 from dual union
select 105,1700,4 from dual)
select
nth_Value(empno,rn) over(order by empno Rows between Unbounded Preceding
                                                 and Unbounded Following) as emp,
nth_Value(sal  ,rn) over(order by sal   Rows between Unbounded Preceding
                                                 and Unbounded Following) as sal
  from t;

EMP  SAL
---  ---
101  720
101  720
101  720
101  720
This post has been answered by Solomon Yakobson on Aug 14 2013
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 18 2013
Added on Aug 18 2010
5 comments
2,080 views