14 lines
827 B
SQL
14 lines
827 B
SQL
CREATE TABLE T1(c1 JSON, c2 INT, c3 REAL);
|
|
CREATE TABLE T2(c1 NUMERIC NOT NULL DEFAULT NULL, c2 NUMERIC);
|
|
CREATE TABLE T3(c1 REAL, c2 REAL, c3 TEXT);
|
|
INSERT INTO T1(c1, c2, c3) VALUES ('{"k": 8}', -256, -493);
|
|
INSERT INTO T1(c1, c2, c3) VALUES ('{"k": 7}', 428, -478);
|
|
INSERT INTO T2(c1, c2) VALUES (532, -622);
|
|
INSERT INTO T2(c1, c2) VALUES (768, -671);
|
|
INSERT INTO T2(c1, c2) VALUES (544, 585);
|
|
INSERT INTO T2(c1, c2) VALUES (-547, 135);
|
|
INSERT INTO T3(c1, c2, c3) VALUES (-644, 226, 194);
|
|
INSERT INTO T3(c1, c2, c3) VALUES (239, -533, 313);
|
|
INSERT INTO T3(c1, c2, c3) VALUES (308, -611, 628);
|
|
SELECT (SELECT ROW_NUMBER() OVER (ORDER BY c.c2) FROM T3 LIMIT 1), DENSE_RANK() OVER (ORDER BY a.c2) FROM T1 AS a INNER JOIN T2 AS b ON a.c1 = b.c2 INNER JOIN T3 AS c ON b.c1 = c.c2 WHERE a.c2 > c.c2 AND b.c1 > a.c1 GROUP BY a.c2;
|