4 Replies Latest reply on Nov 18, 2013 11:33 PM by Peter vd Zwan

# The nth_value function doesn't work as I expected.

Hi all,

Reffer to:

SQL Puzzler: Line Up Column-Wise Rankings

The problem is to get the Nth value from a list col_2_value ordered by col_2_rank. N is given in Col_1_rank.

I thougth I had a simple solution with nth_value function like this:

with tab as
(
select 1 col_1_rank, 'AAA' col_1_value, 3 col_2_rank, 'HHH' col_2_value from dual union all
select 2, 'BBB', 2, 'GGG'  from dual union all
select 3, 'CCC', 5, 'JJJ' from dual union all
select 4, 'DDD', 1, 'FFF' from dual union all
select 5, 'EEE', 4, 'III' from dual
)
select
col_1_rank
,col_1_value
,nth_value(col_2_value, col_1_rank) over (order by col_2_rank rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) a

,case col_1_rank
when 1 then nth_value(col_2_value,1) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 2 then nth_value(col_2_value,2) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 3 then nth_value(col_2_value,3) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 4 then nth_value(col_2_value,4) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 5 then nth_value(col_2_value,5) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
end zz

from
tab

order by
col_1_rank
;

COL_1_RANK COL_1_VALUE A   ZZ

---------- ----------- --- ---

1 AAA         III FFF

2 BBB         III GGG

3 CCC         III HHH

4 DDD         III III

5 EEE         III JJJ

I added column ZZ for checking.

Can any one explain why column A shows the same value on each row while the given N is diffent each time?

I expected that column A and column ZZ would return the same.

If I use a case and a hard coded "Nth" number it works.

My database:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Regards,

Peter

• ###### 1. Re: The nth_value function doesn't work as I expected.

It works in 12c, could be a bug?

• ###### 2. Re: The nth_value function doesn't work as I expected.

Hi,

That is what I thougth as well. According the manual you can use an expresion, column, constant or bind variable for N.

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, as long as it

resolves to a positive integer. The function returns NULL if the data source window

has fewer than n rows. If n is null, then the function returns an error.

Regards,

Peter

• ###### 3. Re: The nth_value function doesn't work as I expected.

https://forums.oracle.com/message/11147654#11147654

Don't use it before 12c :-)

Regards,

Message was edited by: 34MCA2K2