create table t1 (
pk int primary key,
a int,
b int,
c real
);
insert into t1 values
(101 , 0, 10, 1.1),
(102 , 0, 10, 2.1),
(103 , 1, 10, 3.1),
(104 , 1, 10, 4.1),
(108 , 2, 10, 5.1),
(105 , 2, 20, 6.1),
(106 , 2, 20, 7.1),
(107 , 2, 20, 8.15),
(109 , 4, 20, 9.15),
(110 , 4, 20, 10.15),
(111 , 5, NULL, 11.15),
(112 , 5, 1, 12.25),
(113 , 5, NULL, 13.35),
(114 , 5, NULL, 14.50),
(115 , 5, NULL, 15.65),
(116 , 6, 1, NULL),
(117 , 6, 1, 10),
(118 , 6, 1, 1.1),
(119 , 6, 1, NULL),
(120 , 6, 1, NULL),
(121 , 6, 1, NULL),
(122 , 6, 1, 2.2),
(123 , 6, 1, 20.1),
(124 , 6, 1, -10.4),
(125 , 6, 1, NULL),
(126 , 6, 1, NULL),
(127 , 6, 1, NULL);
select pk, a, b, min(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
max(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
from t1;
pk	a	b	min	max
101	0	10	10	10
102	0	10	10	10
103	1	10	10	10
104	1	10	10	10
105	2	20	20	20
106	2	20	20	20
107	2	20	10	20
108	2	10	10	20
109	4	20	20	20
110	4	20	20	20
111	5	NULL	1	1
112	5	1	1	1
113	5	NULL	1	1
114	5	NULL	NULL	NULL
115	5	NULL	NULL	NULL
116	6	1	1	1
117	6	1	1	1
118	6	1	1	1
119	6	1	1	1
120	6	1	1	1
121	6	1	1	1
122	6	1	1	1
123	6	1	1	1
124	6	1	1	1
125	6	1	1	1
126	6	1	1	1
127	6	1	1	1
select pk, a, c, min(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
max(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
from t1;
pk	a	c	min	max
101	0	1.1	1.1	2.1
102	0	2.1	1.1	2.1
103	1	3.1	3.1	4.1
104	1	4.1	3.1	4.1
105	2	6.1	6.1	7.1
106	2	7.1	6.1	8.15
107	2	8.15	5.1	8.15
108	2	5.1	5.1	8.15
109	4	9.15	9.15	10.15
110	4	10.15	9.15	10.15
111	5	11.15	11.15	12.25
112	5	12.25	11.15	13.35
113	5	13.35	12.25	14.5
114	5	14.5	13.35	15.65
115	5	15.65	14.5	15.65
116	6	NULL	10	10
117	6	10	1.1	10
118	6	1.1	1.1	10
119	6	NULL	1.1	1.1
120	6	NULL	NULL	NULL
121	6	NULL	2.2	2.2
122	6	2.2	2.2	20.1
123	6	20.1	-10.4	20.1
124	6	-10.4	-10.4	20.1
125	6	NULL	-10.4	-10.4
126	6	NULL	NULL	NULL
127	6	NULL	NULL	NULL
create table t2 (
pk int primary key,
a int,
b int,
c char(10)
);
insert into t2 values
( 1, 0,  1, 'one'),
( 2, 0,  2, 'two'),
( 3, 0,  3, 'three'),
( 4, 1, 20, 'four'),
( 5, 1, 10, 'five'),
( 6, 1, 40, 'six'),
( 7, 1, 30, 'seven'),
( 8, 4,300, 'eight'),
( 9, 4,100, 'nine'),
(10, 4,200, 'ten'),
(11, 4,200, 'eleven');
# First try some invalid argument queries.
select pk, a, b, c,
min(c) over (order by pk),
max(c) over (order by pk),
min(c) over (partition by a order by pk),
max(c) over (partition by a order by pk)
from t2;
pk	a	b	c	min(c) over (order by pk)	max(c) over (order by pk)	min(c) over (partition by a order by pk)	max(c) over (partition by a order by pk)
1	0	1	one	one	one	one	one
2	0	2	two	one	two	one	two
3	0	3	three	one	two	one	two
4	1	20	four	four	two	four	four
5	1	10	five	five	two	five	four
6	1	40	six	five	two	five	six
7	1	30	seven	five	two	five	six
8	4	300	eight	eight	two	eight	eight
9	4	100	nine	eight	two	eight	nine
10	4	200	ten	eight	two	eight	ten
11	4	200	eleven	eight	two	eight	ten
# Empty frame
select pk, a, b, c,
min(b) over (order by pk rows between 2 following and 1 following) as min1,
max(b) over (order by pk rows between 2 following and 1 following) as max1,
min(b) over (partition by a order by pk rows between 2 following and 1 following) as min2,
max(b) over (partition by a order by pk rows between 2 following and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 2 following and 1 following) as min1,
max(b) over (order by pk range between 2 following and 1 following) as max1,
min(b) over (partition by a order by pk range between 2 following and 1 following) as min2,
max(b) over (partition by a order by pk range between 2 following and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk rows between 1 preceding and 2 preceding) as min1,
max(b) over (order by pk rows between 1 preceding and 2 preceding) as max1,
min(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as min2,
max(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 1 preceding and 2 preceding) as min1,
max(b) over (order by pk range between 1 preceding and 2 preceding) as max1,
min(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as min2,
max(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk rows between 1 following and 0 following) as min1,
max(b) over (order by pk rows between 1 following and 0 following) as max1,
min(b) over (partition by a order by pk rows between 1 following and 0 following) as min2,
max(b) over (partition by a order by pk rows between 1 following and 0 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 1 following and 0 following) as min1,
max(b) over (order by pk range between 1 following and 0 following) as max1,
min(b) over (partition by a order by pk range between 1 following and 0 following) as min2,
max(b) over (partition by a order by pk range between 1 following and 0 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk rows between 1 following and 0 preceding) as min1,
max(b) over (order by pk rows between 1 following and 0 preceding) as max1,
min(b) over (partition by a order by pk rows between 1 following and 0 preceding) as min2,
max(b) over (partition by a order by pk rows between 1 following and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 1 following and 0 preceding) as min1,
max(b) over (order by pk range between 1 following and 0 preceding) as max1,
min(b) over (partition by a order by pk range between 1 following and 0 preceding) as min2,
max(b) over (partition by a order by pk range between 1 following and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk rows between 0 following and 1 preceding) as min1,
max(b) over (order by pk rows between 0 following and 1 preceding) as max1,
min(b) over (partition by a order by pk rows between 0 following and 1 preceding) as min2,
max(b) over (partition by a order by pk rows between 0 following and 1 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 0 following and 1 preceding) as min1,
max(b) over (order by pk range between 0 following and 1 preceding) as max1,
min(b) over (partition by a order by pk range between 0 following and 1 preceding) as min2,
max(b) over (partition by a order by pk range between 0 following and 1 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	NULL	NULL	NULL	NULL
5	1	10	five	NULL	NULL	NULL	NULL
6	1	40	six	NULL	NULL	NULL	NULL
7	1	30	seven	NULL	NULL	NULL	NULL
8	4	300	eight	NULL	NULL	NULL	NULL
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
# 1 row frame.
select pk, a, b, c,
min(b) over (order by pk rows between current row and current row) as min1,
max(b) over (order by pk rows between current row and current row) as max1,
min(b) over (partition by a order by pk rows between current row and current row) as min2,
max(b) over (partition by a order by pk rows between current row and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 0 preceding and current row) as min1,
max(b) over (order by pk rows between 0 preceding and current row) as max1,
min(b) over (partition by a order by pk rows between 0 preceding and current row) as min2,
max(b) over (partition by a order by pk rows between 0 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 0 preceding and 0 preceding) as min1,
max(b) over (order by pk rows between 0 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 1 preceding and 1 preceding) as min1,
max(b) over (order by pk rows between 1 preceding and 1 preceding) as max1,
min(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as min2,
max(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	1	1	1	1
3	0	3	three	2	2	2	2
4	1	20	four	3	3	NULL	NULL
5	1	10	five	20	20	20	20
6	1	40	six	10	10	10	10
7	1	30	seven	40	40	40	40
8	4	300	eight	30	30	NULL	NULL
9	4	100	nine	300	300	300	300
10	4	200	ten	100	100	100	100
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 1 following and 1 following) as min1,
max(b) over (order by pk rows between 1 following and 1 following) as max1,
min(b) over (partition by a order by pk rows between 1 following and 1 following) as min2,
max(b) over (partition by a order by pk rows between 1 following and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	2	2	2	2
2	0	2	two	3	3	3	3
3	0	3	three	20	20	NULL	NULL
4	1	20	four	10	10	10	10
5	1	10	five	40	40	40	40
6	1	40	six	30	30	30	30
7	1	30	seven	300	300	NULL	NULL
8	4	300	eight	100	100	100	100
9	4	100	nine	200	200	200	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	NULL	NULL	NULL	NULL
# Try a larger offset.
select pk, a, b, c,
min(b) over (order by pk rows between 3 following and 3 following) as min1,
max(b) over (order by pk rows between 3 following and 3 following) as max1,
min(b) over (partition by a order by pk rows between 3 following and 3 following) as min2,
max(b) over (partition by a order by pk rows between 3 following and 3 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	20	20	NULL	NULL
2	0	2	two	10	10	NULL	NULL
3	0	3	three	40	40	NULL	NULL
4	1	20	four	30	30	30	30
5	1	10	five	300	300	NULL	NULL
6	1	40	six	100	100	NULL	NULL
7	1	30	seven	200	200	NULL	NULL
8	4	300	eight	200	200	200	200
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk rows between 3 preceding and 3 preceding) as min1,
max(b) over (order by pk rows between 3 preceding and 3 preceding) as max1,
min(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as min2,
max(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	1	1	NULL	NULL
5	1	10	five	2	2	NULL	NULL
6	1	40	six	3	3	NULL	NULL
7	1	30	seven	20	20	20	20
8	4	300	eight	10	10	NULL	NULL
9	4	100	nine	40	40	NULL	NULL
10	4	200	ten	30	30	NULL	NULL
11	4	200	eleven	300	300	300	300
# 2 row frame.
select pk, a, b, c,
min(b) over (order by pk rows between current row and 1 following) as min1,
max(b) over (order by pk rows between current row and 1 following) as max1,
min(b) over (partition by a order by pk rows between current row and 1 following) as min2,
max(b) over (partition by a order by pk rows between current row and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	2	3	2	3
3	0	3	three	3	20	3	3
4	1	20	four	10	20	10	20
5	1	10	five	10	40	10	40
6	1	40	six	30	40	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 0 preceding and 1 following) as min1,
max(b) over (order by pk rows between 0 preceding and 1 following) as max1,
min(b) over (partition by a order by pk rows between 0 preceding and 1 following) as min2,
max(b) over (partition by a order by pk rows between 0 preceding and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	2	3	2	3
3	0	3	three	3	20	3	3
4	1	20	four	10	20	10	20
5	1	10	five	10	40	10	40
6	1	40	six	30	40	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 1 preceding and current row) as min1,
max(b) over (order by pk rows between 1 preceding and current row) as max1,
min(b) over (partition by a order by pk rows between 1 preceding and current row) as min2,
max(b) over (partition by a order by pk rows between 1 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	2	3	2	3
4	1	20	four	3	20	20	20
5	1	10	five	10	20	10	20
6	1	40	six	10	40	10	40
7	1	30	seven	30	40	30	40
8	4	300	eight	30	300	300	300
9	4	100	nine	100	300	100	300
10	4	200	ten	100	200	100	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 1 preceding and 0 preceding) as min1,
max(b) over (order by pk rows between 1 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	2	3	2	3
4	1	20	four	3	20	20	20
5	1	10	five	10	20	10	20
6	1	40	six	10	40	10	40
7	1	30	seven	30	40	30	40
8	4	300	eight	30	300	300	300
9	4	100	nine	100	300	100	300
10	4	200	ten	100	200	100	200
11	4	200	eleven	200	200	200	200
# Try a larger frame/offset.
select pk, a, b, c,
min(b) over (order by pk rows between current row and 3 following) as min1,
max(b) over (order by pk rows between current row and 3 following) as max1,
min(b) over (partition by a order by pk rows between current row and 3 following) as min2,
max(b) over (partition by a order by pk rows between current row and 3 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	20	1	3
2	0	2	two	2	20	2	3
3	0	3	three	3	40	3	3
4	1	20	four	10	40	10	40
5	1	10	five	10	300	10	40
6	1	40	six	30	300	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk rows between 2 preceding and 1 following) as min1,
max(b) over (order by pk rows between 2 preceding and 1 following) as max1,
min(b) over (partition by a order by pk rows between 2 preceding and 1 following) as min2,
max(b) over (partition by a order by pk rows between 2 preceding and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	1	3	1	3
3	0	3	three	1	20	1	3
4	1	20	four	2	20	10	20
5	1	10	five	3	40	10	40
6	1	40	six	10	40	10	40
7	1	30	seven	10	300	10	40
8	4	300	eight	30	300	100	300
9	4	100	nine	30	300	100	300
10	4	200	ten	100	300	100	300
11	4	200	eleven	100	200	100	200
select pk, a, b, c,
min(b) over (order by pk rows between 3 preceding and current row) as min1,
max(b) over (order by pk rows between 3 preceding and current row) as max1,
min(b) over (partition by a order by pk rows between 3 preceding and current row) as min2,
max(b) over (partition by a order by pk rows between 3 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	1	3	1	3
4	1	20	four	1	20	20	20
5	1	10	five	2	20	10	20
6	1	40	six	3	40	10	40
7	1	30	seven	10	40	10	40
8	4	300	eight	10	300	300	300
9	4	100	nine	30	300	100	300
10	4	200	ten	30	300	100	300
11	4	200	eleven	100	300	100	300
select pk, a, b, c,
min(b) over (order by pk rows between 3 preceding and 0 preceding) as min1,
max(b) over (order by pk rows between 3 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	1	3	1	3
4	1	20	four	1	20	20	20
5	1	10	five	2	20	10	20
6	1	40	six	3	40	10	40
7	1	30	seven	10	40	10	40
8	4	300	eight	10	300	300	300
9	4	100	nine	30	300	100	300
10	4	200	ten	30	300	100	300
11	4	200	eleven	100	300	100	300
# Check range frame bounds
select pk, a, b, c,
min(b) over (order by pk range between current row and current row) as min1,
max(b) over (order by pk range between current row and current row) as max1,
min(b) over (partition by a order by pk range between current row and current row) as min2,
max(b) over (partition by a order by pk range between current row and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 0 preceding and current row) as min1,
max(b) over (order by pk range between 0 preceding and current row) as max1,
min(b) over (partition by a order by pk range between 0 preceding and current row) as min2,
max(b) over (partition by a order by pk range between 0 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 0 preceding and 0 preceding) as min1,
max(b) over (order by pk range between 0 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	2	2	2	2
3	0	3	three	3	3	3	3
4	1	20	four	20	20	20	20
5	1	10	five	10	10	10	10
6	1	40	six	40	40	40	40
7	1	30	seven	30	30	30	30
8	4	300	eight	300	300	300	300
9	4	100	nine	100	100	100	100
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 1 preceding and 1 preceding) as min1,
max(b) over (order by pk range between 1 preceding and 1 preceding) as max1,
min(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as min2,
max(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	1	1	1	1
3	0	3	three	2	2	2	2
4	1	20	four	3	3	NULL	NULL
5	1	10	five	20	20	20	20
6	1	40	six	10	10	10	10
7	1	30	seven	40	40	40	40
8	4	300	eight	30	30	NULL	NULL
9	4	100	nine	300	300	300	300
10	4	200	ten	100	100	100	100
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 1 following and 1 following) as min1,
max(b) over (order by pk range between 1 following and 1 following) as max1,
min(b) over (partition by a order by pk range between 1 following and 1 following) as min2,
max(b) over (partition by a order by pk range between 1 following and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	2	2	2	2
2	0	2	two	3	3	3	3
3	0	3	three	20	20	NULL	NULL
4	1	20	four	10	10	10	10
5	1	10	five	40	40	40	40
6	1	40	six	30	30	30	30
7	1	30	seven	300	300	NULL	NULL
8	4	300	eight	100	100	100	100
9	4	100	nine	200	200	200	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	NULL	NULL	NULL	NULL
# Try a larger offset.
select pk, a, b, c,
min(b) over (order by pk range between 3 following and 3 following) as min1,
max(b) over (order by pk range between 3 following and 3 following) as max1,
min(b) over (partition by a order by pk range between 3 following and 3 following) as min2,
max(b) over (partition by a order by pk range between 3 following and 3 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	20	20	NULL	NULL
2	0	2	two	10	10	NULL	NULL
3	0	3	three	40	40	NULL	NULL
4	1	20	four	30	30	30	30
5	1	10	five	300	300	NULL	NULL
6	1	40	six	100	100	NULL	NULL
7	1	30	seven	200	200	NULL	NULL
8	4	300	eight	200	200	200	200
9	4	100	nine	NULL	NULL	NULL	NULL
10	4	200	ten	NULL	NULL	NULL	NULL
11	4	200	eleven	NULL	NULL	NULL	NULL
select pk, a, b, c,
min(b) over (order by pk range between 3 preceding and 3 preceding) as min1,
max(b) over (order by pk range between 3 preceding and 3 preceding) as max1,
min(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as min2,
max(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	NULL	NULL	NULL	NULL
2	0	2	two	NULL	NULL	NULL	NULL
3	0	3	three	NULL	NULL	NULL	NULL
4	1	20	four	1	1	NULL	NULL
5	1	10	five	2	2	NULL	NULL
6	1	40	six	3	3	NULL	NULL
7	1	30	seven	20	20	20	20
8	4	300	eight	10	10	NULL	NULL
9	4	100	nine	40	40	NULL	NULL
10	4	200	ten	30	30	NULL	NULL
11	4	200	eleven	300	300	300	300
# 2 row frame.
select pk, a, b, c,
min(b) over (order by pk range between current row and 1 following) as min1,
max(b) over (order by pk range between current row and 1 following) as max1,
min(b) over (partition by a order by pk range between current row and 1 following) as min2,
max(b) over (partition by a order by pk range between current row and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	2	3	2	3
3	0	3	three	3	20	3	3
4	1	20	four	10	20	10	20
5	1	10	five	10	40	10	40
6	1	40	six	30	40	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 0 preceding and 1 following) as min1,
max(b) over (order by pk range between 0 preceding and 1 following) as max1,
min(b) over (partition by a order by pk range between 0 preceding and 1 following) as min2,
max(b) over (partition by a order by pk range between 0 preceding and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	2	3	2	3
3	0	3	three	3	20	3	3
4	1	20	four	10	20	10	20
5	1	10	five	10	40	10	40
6	1	40	six	30	40	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 1 preceding and current row) as min1,
max(b) over (order by pk range between 1 preceding and current row) as max1,
min(b) over (partition by a order by pk range between 1 preceding and current row) as min2,
max(b) over (partition by a order by pk range between 1 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	2	3	2	3
4	1	20	four	3	20	20	20
5	1	10	five	10	20	10	20
6	1	40	six	10	40	10	40
7	1	30	seven	30	40	30	40
8	4	300	eight	30	300	300	300
9	4	100	nine	100	300	100	300
10	4	200	ten	100	200	100	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 1 preceding and 0 preceding) as min1,
max(b) over (order by pk range between 1 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	2	3	2	3
4	1	20	four	3	20	20	20
5	1	10	five	10	20	10	20
6	1	40	six	10	40	10	40
7	1	30	seven	30	40	30	40
8	4	300	eight	30	300	300	300
9	4	100	nine	100	300	100	300
10	4	200	ten	100	200	100	200
11	4	200	eleven	200	200	200	200
# Try a larger frame/offset.
select pk, a, b, c,
min(b) over (order by pk range between current row and 3 following) as min1,
max(b) over (order by pk range between current row and 3 following) as max1,
min(b) over (partition by a order by pk range between current row and 3 following) as min2,
max(b) over (partition by a order by pk range between current row and 3 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	20	1	3
2	0	2	two	2	20	2	3
3	0	3	three	3	40	3	3
4	1	20	four	10	40	10	40
5	1	10	five	10	300	10	40
6	1	40	six	30	300	30	40
7	1	30	seven	30	300	30	30
8	4	300	eight	100	300	100	300
9	4	100	nine	100	200	100	200
10	4	200	ten	200	200	200	200
11	4	200	eleven	200	200	200	200
select pk, a, b, c,
min(b) over (order by pk range between 2 preceding and 1 following) as min1,
max(b) over (order by pk range between 2 preceding and 1 following) as max1,
min(b) over (partition by a order by pk range between 2 preceding and 1 following) as min2,
max(b) over (partition by a order by pk range between 2 preceding and 1 following) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	2	1	2
2	0	2	two	1	3	1	3
3	0	3	three	1	20	1	3
4	1	20	four	2	20	10	20
5	1	10	five	3	40	10	40
6	1	40	six	10	40	10	40
7	1	30	seven	10	300	10	40
8	4	300	eight	30	300	100	300
9	4	100	nine	30	300	100	300
10	4	200	ten	100	300	100	300
11	4	200	eleven	100	200	100	200
select pk, a, b, c,
min(b) over (order by pk range between 3 preceding and current row) as min1,
max(b) over (order by pk range between 3 preceding and current row) as max1,
min(b) over (partition by a order by pk range between 3 preceding and current row) as min2,
max(b) over (partition by a order by pk range between 3 preceding and current row) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	1	3	1	3
4	1	20	four	1	20	20	20
5	1	10	five	2	20	10	20
6	1	40	six	3	40	10	40
7	1	30	seven	10	40	10	40
8	4	300	eight	10	300	300	300
9	4	100	nine	30	300	100	300
10	4	200	ten	30	300	100	300
11	4	200	eleven	100	300	100	300
select pk, a, b, c,
min(b) over (order by pk range between 3 preceding and 0 preceding) as min1,
max(b) over (order by pk range between 3 preceding and 0 preceding) as max1,
min(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as min2,
max(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as max2
from t2;
pk	a	b	c	min1	max1	min2	max2
1	0	1	one	1	1	1	1
2	0	2	two	1	2	1	2
3	0	3	three	1	3	1	3
4	1	20	four	1	20	20	20
5	1	10	five	2	20	10	20
6	1	40	six	3	40	10	40
7	1	30	seven	10	40	10	40
8	4	300	eight	10	300	300	300
9	4	100	nine	30	300	100	300
10	4	200	ten	30	300	100	300
11	4	200	eleven	100	300	100	300
drop table t2;
drop table t1;