1create table t1 (
2  pk int primary key,
3  a int,
4  b int,
5  c char(10),
6  d decimal(10, 3),
7  e real
8);
9
10insert into t1 values
11( 1, 0, 1,    'one',    0.1,  0.001),
12( 2, 0, 2,    'two',    0.2,  0.002),
13( 3, 0, 3,    'three',  0.3,  0.003),
14( 4, 1, 2,    'three',  0.4,  0.004),
15( 5, 1, 1,    'two',    0.5,  0.005),
16( 6, 1, 1,    'one',    0.6,  0.006),
17( 7, 2, NULL, 'n_one',  0.5,  0.007),
18( 8, 2, 1,    'n_two',  NULL, 0.008),
19( 9, 2, 2,    NULL,     0.7,  0.009),
20(10, 2, 0,    'n_four', 0.8,  0.010),
21(11, 2, 10,   NULL,     0.9,  NULL);
22
23select pk, row_number() over () from t1;
24explain FORMAT=JSON select pk, row_number() over () from t1;
25explain FORMAT=JSON select row_number() over (), pk from t1;
26
27select row_number() over () from (select 4) as t;
28
29--sorted_result
30select min(a) over (), max(a) over (), a, row_number() over ()
31from t1
32where a = 0;
33
34--sorted_result
35select a, min(a) over (), max(a) over (), row_number() over ()
36from t1
37where a = 0;
38
39--sorted_result
40select min(a) over () + 1, max(a) over (), row_number() over ()
41from t1
42where a = 0;
43
44--sorted_result
45select min(a) over () + a, max(a) over (), row_number() over ()
46from t1
47where a = 1;
48
49--sorted_result
50select a + min(a) over (), max(a) over (), row_number() over ()
51from t1
52where a = 1;
53
54select a + min(a) over () from t1 where a = 1;
55
56create view win_view
57as (select a, min(a) over () from t1 where a = 1);
58select * from win_view;
59drop view win_view;
60
61create view win_view
62as (select a, max(a + 1) over () from t1 where a = 1);
63select * from win_view;
64drop view win_view;
65
66drop table t1;
67