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
36
37
38
39
40
41
42
43
|
# name: test/sql/binder/order_by_view.test
# description: Test ORDER BY a view with DISTINCT ON
# group: [binder]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE src("Name" VARCHAR, CreatedAt TIMESTAMP, userID VARCHAR, "Version" VARCHAR, Clients BIGINT, HasDocumentation BOOLEAN, HasCustomAddress BOOLEAN, HasHostname BOOLEAN, RunningContainers BIGINT, HasActions BOOLEAN);
statement ok
CREATE VIEW model AS
SELECT DISTINCT on(userID, CreatedAt::DATE)
CreatedAt::DATE AS CreatedAt,
"Version",
Clients,
HasCustomAddress,
HasHostname,
RunningContainers,
HasDocumentation,
HasActions
FROM src
WHERE name = 'events'
ORDER BY userID, CreatedAt DESC;
statement ok
SELECT HasCustomAddress, count(*) AS total_records
FROM model
WHERE
1 = 1 AND
CreatedAt >= '2023-12-01' AND
CreatedAt < '2023-12-13'
GROUP BY HasCustomAddress ;
statement ok
SELECT HasCustomAddress, count(*) AS total_records
FROM model
WHERE 1 = 1 AND
CreatedAt >= '2023-12-01' AND
CreatedAt < '2023-12-13'
GROUP BY HasCustomAddress
ORDER BY true, total_records DESC NULLS LAST
LIMIT 250 OFFSET 0;
|
