EVOLUTION-MANAGER

Edit File: win_nth_value.result

create table t1 ( pk int primary key, a int, b int, c char(10), d decimal(10, 3), e real ); insert into t1 values ( 1, 0, 1, 'one', 0.1, 0.001), ( 2, 0, 2, 'two', 0.2, 0.002), ( 3, 0, 3, 'three', 0.3, 0.003), ( 4, 1, 2, 'three', 0.4, 0.004), ( 5, 1, 1, 'two', 0.5, 0.005), ( 6, 1, 1, 'one', 0.6, 0.006), ( 7, 2, NULL, 'n_one', 0.5, 0.007), ( 8, 2, 1, 'n_two', NULL, 0.008), ( 9, 2, 2, NULL, 0.7, 0.009), (10, 2, 0, 'n_four', 0.8, 0.010), (11, 2, 10, NULL, 0.9, NULL); select pk, nth_value(pk, 1) over (order by pk), nth_value(pk, 2) over (order by pk), nth_value(pk, 0) over (order by pk), nth_value(pk, -1) over (order by pk), nth_value(pk, -2) over (order by pk) from t1 order by pk asc; pk nth_value(pk, 1) over (order by pk) nth_value(pk, 2) over (order by pk) nth_value(pk, 0) over (order by pk) nth_value(pk, -1) over (order by pk) nth_value(pk, -2) over (order by pk) 1 1 NULL NULL NULL NULL 2 1 2 NULL NULL NULL 3 1 2 NULL NULL NULL 4 1 2 NULL NULL NULL 5 1 2 NULL NULL NULL 6 1 2 NULL NULL NULL 7 1 2 NULL NULL NULL 8 1 2 NULL NULL NULL 9 1 2 NULL NULL NULL 10 1 2 NULL NULL NULL 11 1 2 NULL NULL NULL select pk, nth_value(pk, pk) over (order by pk), nth_value(pk / 0.1, pk) over (order by pk) from t1 order by pk asc; pk nth_value(pk, pk) over (order by pk) nth_value(pk / 0.1, pk) over (order by pk) 1 1 10.0000 2 2 20.0000 3 3 30.0000 4 4 40.0000 5 5 50.0000 6 6 60.0000 7 7 70.0000 8 8 80.0000 9 9 90.0000 10 10 100.0000 11 11 110.0000 select pk, a, nth_value(pk, pk) over (partition by a order by pk), nth_value(pk, a + 1) over (partition by a order by pk) from t1 order by pk asc; pk a nth_value(pk, pk) over (partition by a order by pk) nth_value(pk, a + 1) over (partition by a order by pk) 1 0 1 1 2 0 2 1 3 0 3 1 4 1 NULL NULL 5 1 NULL 5 6 1 NULL 5 7 2 NULL NULL 8 2 NULL NULL 9 2 NULL 9 10 2 NULL 9 11 2 NULL 9 select pk, a, nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) from t1; pk a nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) 1 0 1 2 0 1 3 0 2 4 1 4 5 1 4 6 1 5 7 2 7 8 2 7 9 2 8 10 2 9 11 2 10 select pk, a, nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following), nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) from t1 order by pk asc; pk a nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) 1 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL 2 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL 3 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL 4 1 0 0 0 1 1 1 2 2 2 2 2 NULL 5 1 0 0 0 1 1 1 2 2 2 2 2 NULL 6 1 0 0 0 1 1 1 2 2 2 2 2 NULL 7 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL 8 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL 9 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL 10 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL 11 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL drop table t1;