1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
# name: test/sql/window/test_order_by_all.test
# description: Window Order By All
# group: [window]
statement ok
PRAGMA enable_verification
statement error
SELECT i, j, ROW_NUMBER() OVER (ORDER BY ALL) AS rn
FROM (
SELECT i ,j
FROM generate_series(1, 5) s(i)
CROSS JOIN generate_series(1, 2) t(j)
) t;
----
Cannot ORDER BY ALL in a window expression
# We CAN order by column patterns
query II
SELECT
rank() OVER (ORDER BY COLUMNS('^(.*)_score$') DESC) AS '\1_rank'
FROM (
SELECT
range AS math_score,
100-range as reading_score
from range(65, 100, 5)
);
----
7 1
6 2
5 3
4 4
3 5
2 6
1 7
|
