summaryrefslogtreecommitdiff
path: root/mysql-test/main/win_min_max.result
diff options
context:
space:
mode:
authorMichael Widenius <monty@mariadb.org>2018-03-09 14:05:35 +0200
committerMonty <monty@mariadb.org>2018-03-29 13:59:44 +0300
commita7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch)
tree70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/win_min_max.result
parentab1941266c59a19703a74b5593cf3f508a5752d7 (diff)
downloadmariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/win_min_max.result')
-rw-r--r--mysql-test/main/win_min_max.result864
1 files changed, 864 insertions, 0 deletions
diff --git a/mysql-test/main/win_min_max.result b/mysql-test/main/win_min_max.result
new file mode 100644
index 00000000000..b3a90262bf7
--- /dev/null
+++ b/mysql-test/main/win_min_max.result
@@ -0,0 +1,864 @@
+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;