📄 orderby.out
字号:
--------------------2222222222222 2222222222222 3333333333333 4444444444444 ij> select l from missed order by l desc;L --------------------4444444444444 3333333333333 2222222222222 2222222222222 ij> rollback ;ij> -- . order by on char columncreate table ut (u char(10));0 rows inserted/updated/deletedij> insert into ut values (null);1 row inserted/updated/deletedij> insert into ut values (cast ('hello' as char(10)));1 row inserted/updated/deletedij> insert into ut values ('world');1 row inserted/updated/deletedij> insert into ut values ('hello');1 row inserted/updated/deletedij> insert into ut values ('world ');1 row inserted/updated/deletedij> -- rollback should release the prepared statementsrollback ;ij> -- . order by and explicit for update (no, some cols)get cursor c1 as 'select i from obt order by i for update of v';ERROR 42Y90: FOR UPDATE is not permitted in this type of statement. ij> -- . order by and explicit read only (ok)get cursor c1 as 'select i from obt order by i for read only';ij> next c1;I -----------1 ij> close c1;ij> -- . order by is implicitly read onlyget cursor c1 as 'select i from obt order by i';ij> next c1;I -----------1 ij> -- errorupdate obt set v='newval' where current of c1;ERROR 42X23: Cursor C1 is not updatable.ij> close c1;ij> -- no rowsselect v from obt where v='newval';V ----------------------------------------ij> -- rollback should release the prepared statementsrollback ;ij> -- . order by only allowed on cursor spec, not subquerys (error) select v from obt where i in (select i from obt2 order by i);ERROR 42X01: Syntax error: Encountered "order" at line 2, column 50.ij> select v from obt where i = (select i from obt2 order by i);ERROR 42X01: Syntax error: Encountered "order" at line 1, column 49.ij> select v from (select i,v from obt2 order by i);ERROR 42X01: Syntax error: Encountered "order" at line 1, column 37.ij> -- rollback should release the prepared statementsrollback ;ij> -- order by allowed on datatypes, -- but not non-mapped user types-- bit maps to Byte[], so can't test for nowcreate table tab1 ( i integer, tn integer, s integer, l integer, c char(10), v char(10), lvc char(10), d double precision, r real, dt date, t time, ts timestamp, dc decimal(2,1));0 rows inserted/updated/deletedij> insert into tab1 values (1, cast(1 as int), cast(1 as smallint), cast(1 as bigint), '1', '1', '1', cast(1.1 as double precision), cast(1.1 as real), '1996-01-01', '11:11:11','xxxxxxFILTERED-TIMESTAMPxxxxx', cast(1.1 as decimal(2,1)));1 row inserted/updated/deletedij> insert into tab1 values (2, cast(2 as int), cast(2 as smallint), cast(2 as bigint), '2', '2', '2', cast(2.2 as double precision), cast(2.2 as real), '1995-02-02', '12:12:12', 'xxxxxxFILTERED-TIMESTAMPxxxxx', cast(2.2 as decimal(2,1)));1 row inserted/updated/deletedij> select * from tab1 order by 1;I |TN |S |L |C |V |LVC |D |R |DT |T |TS |DC --------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 |1 |1 |1 |1 |1 |1 |1.1 |1.1 |1996-01-01|11:11:11|xxxxxxFILTERED-TIMESTAMPxxxxx|1.1 2 |2 |2 |2 |2 |2 |2 |2.2 |2.2 |1995-02-02|12:12:12|xxxxxxFILTERED-TIMESTAMPxxxxx|2.2 ij> rollback;ij> -- bug 2769 (correlation columns, group by and order by)create table bug2769(c1 int, c2 int);0 rows inserted/updated/deletedij> insert into bug2769 values (1, 1), (1, 2), (3, 2), (3, 3);4 rows inserted/updated/deletedij> select a.c1, sum(a.c1) from bug2769 a group by a.c1 order by a.c1;C1 |2 -----------------------1 |2 3 |6 ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by bug2769.c1;X |Y -----------------------1 |2 3 |6 ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by x;X |Y -----------------------1 |2 3 |6 ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1 + c2;X |Y -----------------------1 |1 1 |2 3 |2 3 |3 ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by -(c1 + c2);X |Y -----------------------3 |3 3 |2 1 |2 1 |1 ij> rollback;ij> -- reset autocommitautocommit on;ij> -- cleanupdrop table obt;0 rows inserted/updated/deletedij> drop table obt2;0 rows inserted/updated/deletedij> create table t (a int, b int, c int);0 rows inserted/updated/deletedij> insert into t values (1, 2, null), (2, 3, null), (3, 0, null), (1, 3, null);4 rows inserted/updated/deletedij> select * from t order by a;A |B |C -----------------------------------1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, a;A |B |C -----------------------------------1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, a, a;A |B |C -----------------------------------1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, b;A |B |C -----------------------------------1 |2 |NULL 1 |3 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, b, c from t order by a, a;A |B |C -----------------------------------1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, b, c from t order by a, b;A |B |C -----------------------------------1 |2 |NULL 1 |3 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, c from t order by b;A |C -----------------------3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, c from t order by b, b;A |C -----------------------3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, b, c from t order by b;A |B |C -----------------------------------3 |0 |NULL 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL ij> select a from t order by b, c;A -----------3 1 1 2 ij> select a, c from t order by b, c;A |C -----------------------3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, c from t order by b, c, b, c;A |C -----------------------3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, b, c from t order by b, c;A |B |C -----------------------------------3 |0 |NULL 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL ij> select b, c from t order by app.t.a;B |C -----------------------3 |NULL 2 |NULL 3 |NULL 0 |NULL ij> --Test addtive expression in order clausecreate table test_word(value varchar(32));0 rows inserted/updated/deletedij> insert into test_word(value) values('anaconda');1 row inserted/updated/deletedij> insert into test_word(value) values('America');1 row inserted/updated/deletedij> insert into test_word(value) values('camel');1 row inserted/updated/deletedij> insert into test_word(value) values('Canada');1 row inserted/updated/deletedij> select * from test_word order by value;VALUE --------------------------------America Canada anaconda camel ij> select * from test_word order by upper(value);VALUE --------------------------------America anaconda camel Canada ij> drop table test_word;0 rows inserted/updated/deletedij> create table test_number(value integer);0 rows inserted/updated/deletedij> insert into test_number(value) values(-1);1 row inserted/updated/deletedij> insert into test_number(value) values(0);1 row inserted/updated/deletedij> insert into test_number(value) values(1);1 row inserted/updated/deletedij> insert into test_number(value) values(2);1 row inserted/updated/deletedij> insert into test_number(value) values(3);1 row inserted/updated/deletedij> insert into test_number(value) values(100);1 row inserted/updated/deletedij> insert into test_number(value) values(1000);1 row inserted/updated/deletedij> select * from test_number order by value;VALUE ------------1 0 1 2 3 100 1000 ij> select * from test_number order by value + 1;VALUE ------------1 0 1 2 3 100 1000 ij> select * from test_number order by value - 1;VALUE ------------1 0 1 2 3 100 1000 ij> select * from test_number order by value * 1;VALUE ------------1 0 1 2 3 100 1000 ij> select * from test_number order by value / 1;VALUE ------------1 0 1 2 3 100 1000 ij> select * from test_number order by 1 + value;VALUE ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -