orderby.sql

derby database source code.good for you.

insert into v values 'itsastart';
insert into v values 'hereandt';
update v set v = v || v || v;
update v set v = v || v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
select v from v order by v desc;
rollback ;
drop table v;

-- . order by on all data types
create table missed (s smallint, r real, d date, t time, ts timestamp, c char(10), l bigint);
insert into missed values (1,1.2e4, '1992-01-01','23:01:01', '1993-02-04 12:02:00.001', 'theend', 2222222222222);
insert into missed values (1,1.2e4, '1992-01-01', '23:01:01', '1993-02-04 12:02:00.001', 'theend', 3333333333333);
insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', 'theend', 4444444444444);
insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', null,     2222222222222);
select s from missed order by s;
select r from missed order by r;
select d,c from missed order by c,d;
select ts,t from missed order by ts desc, t;
select l from missed order by l;
select l from missed order by l desc;
rollback ;

-- . order by on char column
create table ut (u char(10));
insert into ut values (null);
insert into ut values (cast ('hello' as char(10)));
insert into ut values ('world');
insert into ut values ('hello');
insert into ut values ('world  ');
-- rollback should release the prepared statements
rollback ;

-- . order by and explicit for update (no, some cols)
get cursor c1 as 'select i from obt order by i for update of v';

-- . order by and explicit read only (ok)
get cursor c1 as 'select i from obt order by i for read only';
next c1;
close c1;

-- . order by is implicitly read only
get cursor c1 as 'select i from obt order by i';
next c1;
-- error
update obt set v='newval' where current of c1;
close c1;
-- no rows
select v from obt where v='newval';

-- rollback should release the prepared statements
rollback ;

-- . order by only allowed on cursor spec, not subquerys (error) 
select v from obt where i in (select i from obt2 order by i);

select v from obt where i = (select i from obt2 order by i);

select v from (select i,v from obt2 order by i);

-- rollback should release the prepared statements
rollback ;

-- order by allowed on datatypes, 
-- but not non-mapped user types
-- bit maps to Byte[], so can't test for now
create 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));
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','1996-01-01 11:10:10.1', cast(1.1 as decimal(2,1)));
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', '1996-02-02 12:10:10.1', cast(2.2 as decimal(2,1)));
select * from tab1 order by 1;
rollback;

-- bug 2769 (correlation columns, group by and order by)
create table bug2769(c1 int, c2 int);
insert into bug2769 values (1, 1), (1, 2), (3, 2), (3, 3);
select a.c1, sum(a.c1) from bug2769 a group by a.c1 order by a.c1;
select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by bug2769.c1;
select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by x;
select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1 + c2;
select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by -(c1 + c2);
rollback;

-- reset autocommit
autocommit on;

-- cleanup
drop table obt;
drop table obt2;

create table t (a int, b int, c int);
insert into t values (1, 2, null), (2, 3, null), (3, 0, null), (1, 3, null);

select * from t order by a;
select * from t order by a, a;
select * from t order by a, a, a;
select * from t order by a, b;

select a, b, c from t order by a, a;
select a, b, c from t order by a, b;

select a, c from t order by b; 
select a, c from t order by b, b; 
select a, b, c from t order by b; 

select a from t order by b, c;
select a, c from t order by b, c;
select a, c from t order by b, c, b, c;
select a, b, c from t order by b, c;
select b, c from t order by app.t.a;


--Test addtive expression in order clause

create table test_word(value varchar(32));
insert into test_word(value) values('anaconda');
insert into test_word(value) values('America');
insert into test_word(value) values('camel');
insert into test_word(value) values('Canada');

select * from test_word order by value;
select * from test_word order by upper(value);

drop table test_word;

create table test_number(value integer);
insert into test_number(value) values(-1);
insert into test_number(value) values(0);
insert into test_number(value) values(1);
insert into test_number(value) values(2);
insert into test_number(value) values(3);
insert into test_number(value) values(100);
insert into test_number(value) values(1000);
select * from test_number order by value;
select * from test_number order by value + 1;
select * from test_number order by value - 1;
select * from test_number order by value * 1;
select * from test_number order by value / 1;
select * from test_number order by 1 + value;
select * from test_number order by 1 - value;
select * from test_number order by 1 * value;
select * from test_number where value <> 0 order by 6000 / value;
select * from test_number order by -1 + value;
select * from test_number order by -1 - value;
select * from test_number order by - 1 * value;
select * from test_number where value <> 0 order by - 6000 / value;
select * from test_number order by abs(value);
select * from test_number order by value desc;
select * from test_number order by value + 1 desc;
select * from test_number order by value - 1 desc;
select * from test_number order by value * 1 desc;
select * from test_number order by value / 1 desc;
select * from test_number order by 1 + value desc;
select * from test_number order by 1 - value desc;
select * from test_number order by 1 * value desc;
select * from test_number where value <> 0 order by 6000 / value desc;
select * from test_number order by -1 + value desc;
select * from test_number order by -1 - value desc;
select * from test_number order by - 1 * value desc;
select * from test_number where value <> 0 order by - 6000 / value desc;
select * from test_number order by abs(value) desc;
drop table test_number;
create table test_number2(value1 integer,value2 integer);
insert into test_number2(value1,value2) values(-2,2);
insert into test_number2(value1,value2) values(-1,2);
insert into test_number2(value1,value2) values(0,1);
insert into test_number2(value1,value2) values(0,2);
insert into test_number2(value1,value2) values(1,1);
insert into test_number2(value1,value2) values(2,1);
select * from test_number2 order by abs(value1),mod(value2,2);
drop table test_number2;
-- error case
select * from t order by d;
select t.* from t order by d;
select t.* from t order by t.d;
select s.* from t s order by s.d;

select *, d from t order by d;
select t.*, d from t order by d;
select t.*, d from t order by t.d;
select t.*, d from t order by app.t.d;
select s.*, d from t s order by s.d;
select t.*, t.d from t order by t.d;
select s.*, s.d from t s order by s.d;


select a, b, c from t order by d;
select a from t order by d;
select t.a from t order by t.d;
select s.a from t s order by s.d;

drop table t;

-- test fof using table correlation names 
select * from (values (2),(1)) as t(x) order by t.x;

create table ta(id int);
create table tb(id int,c1 int,c2 int);
insert into ta(id)  values(1);
insert into ta(id)  values(2);
insert into ta(id)  values(3);
insert into ta(id)  values(4);
insert into ta(id)  values(5);
insert into tb(id,c1,c2) values(1,5,3);
insert into tb(id,c1,c2) values(2,4,3);
insert into tb(id,c1,c2) values(3,4,2);
insert into tb(id,c1,c2) values(4,4,1);
insert into tb(id,c1,c2) values(5,4,2);
select t1.id,t2.c1 from ta as t1 join tb as t2 on t1.id = t2.id order by t2.c1,t2.c2,t1.id;

drop table ta;
drop table tb;