create table XXX
(
xxx_id VARCHAR2(20),
xxx_level VARCHAR2(20),
xxx_finish VARCHAR2(20),
level_id VARCHAR2(20)
);
create table YYY
(
yyy_id VARCHAR2(20),
yyy_level VARCHAR2(20),
yyy_finish VARCHAR2(20)
);
prompt Importing table xxx...
set feedback off
set define off
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('1', 'level1', '30', null, 'AAASRcAAEAAABgLAAA');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('3', 'level2', '40', null, 'AAASRcAAEAAABgLAAB');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('7', 'level1', '60', null, 'AAASRcAAEAAABgLAAC');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('6', 'level1', '50', null, 'AAASRcAAEAAABgLAAD');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('9', 'level3', '30', null, 'AAASRcAAEAAABgLAAE');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('8', 'level3', '40', null, 'AAASRcAAEAAABgLAAF');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('10', 'level3', '50', null, 'AAASRcAAEAAABgLAAG');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('11', 'level1', '70', null, 'AAASRcAAEAAABgLAAH');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('4', 'level2', '50', null, 'AAASRcAAEAAABgLAAI');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('5', 'level2', '10', null, 'AAASRcAAEAAABgLAAJ');
insert into xxx (XXX_ID, XXX_LEVEL, XXX_FINISH, LEVEL_ID, ROWID)
values ('2', 'level2', '20', null, 'AAASRcAAEAAABgLAAK');
prompt Done.
prompt Importing table yyy...
set feedback off
set define off
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('2', 'level1', '30', 'AAASRdAAEAAABgTAAA');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('1', 'level1', '20', 'AAASRdAAEAAABgTAAB');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('3', 'level1', '40', 'AAASRdAAEAAABgTAAC');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('4', 'level1', '50', 'AAASRdAAEAAABgTAAD');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('1', 'level2', '20', 'AAASRdAAEAAABgTAAE');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('2', 'level2', '30', 'AAASRdAAEAAABgTAAF');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('3', 'level2', '40', 'AAASRdAAEAAABgTAAG');
insert into yyy (YYY_ID, YYY_LEVEL, YYY_FINISH, ROWID)
values ('4', 'level2', '50', 'AAASRdAAEAAABgTAAH');
prompt Done.
select min(x.xxx_finish),y.yyy_finish from xxx x ,yyy y where x.xxx_level = y.yyy_level and x.xxx_finish >= y.yyy_finish group by y.yyy_level, y.yyy_finish;
select y.*,(select min(xxx_finish),x.xxx_level from xxx x where x.xxx_finish > y.yyy_finish and y.yyy_level = x.xxx_level group by x.xxx_level) from yyy y ;