Oracle Analytic Functions: FIRST_VALUE, NTH_VALUE etc.

NTH_VALUE

create table tq84_nth_value (
   i    number,
   a    varchar2(10)
);

insert into tq84_nth_value values (11, 'eleven'    );
insert into tq84_nth_value values ( 3, 'three'     );
insert into tq84_nth_value values (21, 'twenty-one');
insert into tq84_nth_value values ( 8, 'eight'     );
insert into tq84_nth_value values ( 9, 'nine'      );
insert into tq84_nth_value values (22, 'twenty-two');
insert into tq84_nth_value values (13, 'thirteen'  );
insert into tq84_nth_value values ( 4, 'four'      );
insert into tq84_nth_value values ( 1, 'one'       );
insert into tq84_nth_value values ( 7, 'seven'     );
insert into tq84_nth_value values (12, 'twelve'    );
insert into tq84_nth_value values ( 5, 'five'      );
insert into tq84_nth_value values (14, 'fourteen'  );
insert into tq84_nth_value values ( 2, 'two'       );
insert into tq84_nth_value values (20, 'twenty'    );

select
-- i,
   a,
   nth_value(a, 3) from first over (order by i                                                          ) p,
   nth_value(a, 3) from first over (order by i range between unbounded preceding and unbounded following) q,
   nth_value(a, 3) from first over (order by i range between 4         preceding and unbounded following) r,
   nth_value(a, 3) from last  over (order by i                                                          ) s,
   nth_value(a, 3) from last  over (order by i range between unbounded preceding and unbounded following) t
from
   tq84_nth_value;
--
-- A          P          Q          R          S          T
-- ---------- ---------- ---------- ---------- ---------- ----------
-- one                   three      three                 twenty
-- two                   three      three                 twenty
-- three      three      three      three      one        twenty
-- four       three      three      three      two        twenty
-- five       three      three      three      three      twenty
-- seven      three      three      five       four       twenty
-- eight      three      three      seven      five       twenty
-- nine       three      three      eight      seven      twenty
-- eleven     three      three      nine       eight      twenty
-- twelve     three      three      eleven     nine       twenty
-- thirteen   three      three      twelve     eleven     twenty
-- fourteen   three      three      thirteen   twelve     twenty
-- twenty     three      three      twenty-two thirteen   twenty
-- twenty-one three      three      twenty-two fourteen   twenty
-- twenty-two three      three      twenty-two twenty     twenty

drop table tq84_nth_value purge;

FIRST_VALUE

create table tq84_first_value (
   a number  (2),
   b varchar2(2)
);

insert into tq84_first_value values (1, 'B');
insert into tq84_first_value values (2, 'C');
insert into tq84_first_value values (3, 'A');
insert into tq84_first_value values (4, 'E');
insert into tq84_first_value values (5, 'D');

select
   a,
   b,
   first_value(a   ) over (order by b) first_a , -- 3 (A is first)
   nth_value  (a, 1) over (order by b) first_a_, -- Same as above, but using nth_value
   first_value(b   ) over (order by a) first_b , -- B (1 is first)
   nth_value  (b, 1) over (order by a) first_b_  -- Same as above, but using nth_value
from
   tq84_first_value;
--
-- 
--   A B  FIRST_A FIRST_A_ FI FI
-- --- -- ------- -------- -- --
--   1 B        3        3 B  B 
--   2 C        3        3 B  B 
--   3 A        3        3 B  B 
--   4 E        3        3 B  B 
--   5 D        3        3 B  B 

drop table tq84_first_value purge;

See also