📄 orderby.out
字号:
ij> -- order by tests-- in V52, we allow "select a from t order by b" where the ORDERBY column doesn't necessarily appear in the SELECT list.autocommit off;ij> -- . order by on valuesvalues (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,2,3;1 |2 |3 -----------------------------------0 |0 |1 0 |1 |0 1 |0 |0 1 |0 |1 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,3;1 |2 |3 -----------------------------------0 |1 |0 0 |0 |1 1 |0 |0 1 |0 |1 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2,1;1 |2 |3 -----------------------------------0 |0 |1 1 |0 |0 1 |0 |1 0 |1 |0 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2;1 |2 |3 -----------------------------------0 |0 |1 1 |0 |0 1 |0 |1 0 |1 |0 ij> -- . order by on position < 1, > range (error)values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 0;ERROR 42X77: Column position '0' is out of range for the query expression.ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 4;ERROR 42X77: Column position '4' is out of range for the query expression.ij> -- . order by doesn't see generated namesvalues (1,0,1),(1,0,0),(0,0,1),(0,1,0);1 |2 |3 -----------------------------------1 |0 |1 1 |0 |0 0 |0 |1 0 |1 |0 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1";ERROR 42X78: Column 'SQLCol1' is not in the result of the query expression.ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol2";ERROR 42X78: Column 'SQLCol2' is not in the result of the query expression.ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,1,2,3;1 |2 |3 -----------------------------------0 |0 |1 0 |1 |0 1 |0 |0 1 |0 |1 ij> -- rollback should release the prepared statementsrollback;ij> -- . order by on select-- . order by with duplicate rows in sourceset schema app;0 rows inserted/updated/deletedij> create table obt (i int, v varchar(40));0 rows inserted/updated/deletedij> insert into obt (i) values (null);1 row inserted/updated/deletedij> insert into obt values (1, 'hello');1 row inserted/updated/deletedij> insert into obt values (2, 'planet');1 row inserted/updated/deletedij> insert into obt values (1, 'world');1 row inserted/updated/deletedij> insert into obt values (3, 'hello');1 row inserted/updated/deletedij> -- save the data we've createdcommit;ij> select * from obt order by i;I |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by v;I |V ----------------------------------------------------3 |hello 1 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by all select columnsselect * from obt order by i,v;I |V ----------------------------------------------------1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> select * from obt order by v,i;I |V ----------------------------------------------------1 |hello 3 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by asc/desc mixselect * from obt order by v desc, i asc;I |V ----------------------------------------------------NULL |NULL 1 |world 2 |planet 1 |hello 3 |hello ij> -- reverse prior orderselect * from obt order by i asc, v desc;I |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicates but different asc/desc attributes (ok)select * from obt order by i asc, i desc;I |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by i, v, i;I |V ----------------------------------------------------1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> select v from obt order by i, v, i;V ----------------------------------------hello world planet hello NULL ij> select v from obt order by i desc, v, i;V ----------------------------------------NULL hello planet hello world ij> -- . order by on position < 1, > range (error)select * from obt order by 1, 0;ERROR 42X77: Column position '0' is out of range for the query expression.ij> select * from obt order by 1,2,3,4,5,6,7,8,9;ERROR 42X77: Column position '3' is out of range for the query expression.ij> select * from obt order by 32767;ERROR 42X77: Column position '32767' is out of range for the query expression.ij> -- rollback should release the prepared statementsrollback ;ij> -- . order by on union allcreate table obt2 (i2 int, v varchar(40));0 rows inserted/updated/deletedij> insert into obt2 values (3, 'hello'), (4, 'planet'), (1, 'shoe'), (3, 'planet');4 rows inserted/updated/deletedij> -- save the data we've createdcommit ;ij> select * from obt union all select * from obt2 order by v;1 |V ----------------------------------------------------3 |hello 3 |hello 1 |hello 3 |planet 4 |planet 2 |planet 1 |shoe 1 |world NULL |NULL ij> select * from obt union all select * from obt order by i;I |V ----------------------------------------------------1 |world 1 |hello 1 |world 1 |hello 2 |planet 2 |planet 3 |hello 3 |hello NULL |NULL NULL |NULL ij> select * from obt union all select * from obt order by i, i;I |V ----------------------------------------------------1 |world 1 |hello 1 |world 1 |hello 2 |planet 2 |planet 3 |hello 3 |hello NULL |NULL NULL |NULL ij> -- . order by on union with differing column names on sources. Errorselect * from obt union all select * from obt2 order by i;ERROR 42X78: Column 'I' is not in the result of the query expression.ij> select * from obt union all values (1,'hello') order by i;ERROR 42X78: Column 'I' is not in the result of the query expression.ij> values (1,'hello') union all select * from obt order by i;ERROR 42X78: Column 'I' is not in the result of the query expression.ij> -- . order by can not see generated names, though OK by positionvalues (1,'hello') union all select * from obt;1 |2 ----------------------------------------------------1 |hello NULL |NULL 1 |hello 2 |planet 1 |world 3 |hello ij> values (1,'hello') union all select * from obt order by "SQLCol1";ERROR 42X78: Column 'SQLCol1' is not in the result of the query expression.ij> values (1,'hello') union all select * from obt order by 1;1 |2 ----------------------------------------------------1 |world 1 |hello 1 |hello 2 |planet 3 |hello NULL |NULL ij> values (1,'hello') union all select * from obt order by 1, 1;1 |2 ----------------------------------------------------1 |world 1 |hello 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- rollback should release the prepared statementsrollback ;ij> select i from obt union all values (1) order by 1;1 -----------1 1 1 2 3 NULL ij> -- sees noname on both sides although second side is namedvalues (1) union all select i from obt order by i;ERROR 42X78: Column 'I' is not in the result of the query expression.ij> -- rollback should release the prepared statementsrollback ;ij> -- i2's name is hidden by obt, failsselect * from obt union all select * from obt2 order by i2;ERROR 42X78: Column 'I2' is not in the result of the query expression.ij> -- . order by position/name mixselect * from obt order by 1,i;I |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by 1,v;I |V ----------------------------------------------------1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicate positionsselect * from obt order by 1,2,1;I |V ----------------------------------------------------1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicate namesselect * from obt order by v,i,v;I |V ----------------------------------------------------1 |hello 3 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by name gets select name, not underlying nameselect i as i2, v from obt order by i2;I2 |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- error, i is not seen by order byselect i as i2, v from obt order by i;I2 |V ----------------------------------------------------1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- rollback should release the prepared statementsrollback ;ij> -- . order without by (error)select i, v from obt order i;ERROR 42X01: Syntax error: Encountered "i" at line 2, column 28.ij> select i, v from obt by i;ERROR 42X01: Syntax error: Encountered "by" at line 1, column 22.⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -