Skip to Main Content
Forums

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

Aketi Jyuuzou
PostgreSQL9.0 returns correct result X-(
postgres=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 9.0beta1, compiled by Visual C++ build 1500, 32-bit
(1 行)

postgres=# with t(empno,sal,rn) as(
postgres(# values(101, 720,1),
postgres(#       (102, 850,2),
postgres(#       (103,1250,3),
postgres(#       (105,1700,4))
postgres-# select
postgres-# nth_Value(empno,rn)
postgres-# over(order by empno Rows between Unbounded Preceding
postgres(#                              and Unbounded Following) as emp,
postgres-# nth_Value(sal  ,rn)
postgres-# over(order by sal   Rows between Unbounded Preceding
postgres(#                              and Unbounded Following) as sal
postgres-#   from t;
 emp | sal
-----+------
 101 |  720
 102 |  850
 103 | 1250
 105 | 1700
Solomon Yakobson
Looks like a bug. The only way I was able to make it work as expected was a combination FROM LAST and ORDER BY DESC:
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 empno,rn,
nth_Value(empno,rn) over(order by empno rows between Unbounded Preceding
                             and Unbounded Following) as emp1,
nth_Value(sal,rn) over(order by sal rows between Unbounded Preceding
                               and Unbounded Following) as sal1,
nth_Value(empno,rn) from first over(order by empno rows between Unbounded Preceding
                             and Unbounded Following) as emp2,
nth_Value(sal,rn) from first over(order by sal rows between Unbounded Preceding
                               and Unbounded Following) as sal2,
nth_Value(empno,rn) from last over(order by empno rows between Unbounded Preceding
                             and Unbounded Following) as emp3,
nth_Value(sal,rn) from last over(order by sal rows between Unbounded Preceding
                               and Unbounded Following) as sal3,
nth_Value(empno,rn) from last over(order by empno desc rows between Unbounded Preceding
                             and Unbounded Following) as emp4,
nth_Value(sal,rn) from last over(order by sal desc rows between Unbounded Preceding
                               and Unbounded Following) as sal4
  from t
order by rn
/

     EMPNO         RN       EMP1       SAL1       EMP2       SAL2       EMP3       SAL3       EMP4       SAL4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       101          1        101        720        101        720        105       1700        101        720
       102          2        101        720        101        720        103       1250        102        850
       103          3        101        720        101        720        102        850        103       1250
       105          4        101        720        101        720        101        720        105       1700

SQL>
Now:
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 empno,sal,rn,
nth_Value(empno,rn) from last over(order by empno desc Rows between Unbounded Preceding
                                                 and Unbounded Following) as emp,
nth_Value(sal  ,rn) from last over(order by sal desc  Rows between Unbounded Preceding
                                                 and Unbounded Following) as sal
  from t
order by rn
/

     EMPNO        SAL         RN        EMP        SAL
---------- ---------- ---------- ---------- ----------
       101        720          1        101        720
       102        850          2        102        850
       103       1250          3        103       1250
       105       1700          4        105       1700

SQL>
SY.
1 person found this helpful
Aketi Jyuuzou
Thanks Solomon Yakobson.
I used from last then it works.
Solomon Yakobson
Answer

Tested Nth_VALUE in 12C. Looks like the bug is fixed:

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

       EMP        SAL
---------- ----------
       101        720
       102        850
       103       1250
       105       1700

SQL>

SY.

Marked as Answer by Aketi Jyuuzou · Sep 27 2020
Aketi Jyuuzou

WOW

Thanks for test in 12c.

1 - 5
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,083 views