4 Replies Latest reply: Nov 18, 2013 5:33 PM by Peter vd Zwan RSS

    The nth_value function doesn't work as I expected.

    Peter vd Zwan

      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