For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Dears, Where can i report apex bugs? Best regards,
If I understand you correctly you need:
SQL> get t 1 with td as ( select to_date('27.11.07 06:00', 'dd.mm.yyyy hh24:mi') my_date, 1 id from dual union all 2 select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 2 from dual union all 3 select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 1 from dual union all 4 select to_date('27.11.07 10:30', 'dd.mm.yyyy hh24:mi'), 2 from dual union all 5 select to_date('27.11.07 10:45', 'dd.mm.yyyy hh24:mi'), 1 from dual union all 6 select to_date('27.11.07 15:00', 'dd.mm.yyyy hh24:mi'), 2 from dual 7 ) 8 -- end of testdata 9 select to_char(max(my_date), 'dd.mm.yyyy hh24:mi') 10 from td 11 where id = 2 12 and my_date < ( select max(my_date) 13 from td 14 where id = 1 15 group by id 16 ) 17* group by id SQL> / TO_CHAR(MAX(MY_D ---------------- 27.11.0007 10:30 SQL>
with x as ( select to_date ('27.11.07 06:00', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all select to_date ('27.11.07 10:00', 'dd.mm.rr hh24:mi') dt, 2 type from dual union all select to_date ('27.11.07 10:00', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all select to_date ('27.11.07 10:30', 'dd.mm.rr hh24:mi') dt, 2 type from dual union all select to_date ('27.11.07 10:45', 'dd.mm.rr hh24:mi') dt, 1 type from dual union all select to_date ('27.11.07 15:00', 'dd.mm.rr hh24:mi') dt, 2 type from dual ) select max_value , (select max (dt) from x where type = 2 and dt < max_value ) min_value from ( select max (dt) max_value from x where type = 1 ) ;
or:
SQL> select * from td; MY_DATE ID ---------------- ---------- 27/11/0007 06:00 1 27/11/0007 10:00 2 27/11/0007 10:00 1 27/11/0007 10:30 2 27/11/0007 10:45 1 27/11/0007 15:00 2 6 rows selected. SQL> select max(decode(id,1,my_date)) mx1, 2 max(decode(id,2,my_date)) mx2 3 from ( select my_date,id,max(decode(id,1,my_date)) over(order by null) mx 4 from td) 5 where my_date < mx 6 or id = 1; MX1 MX2 ---------------- ---------------- 27/11/0007 10:45 27/11/0007 10:30
with td as ( select to_date('27.11.07 06:00', 'dd.mm.yyyy hh24:mi') my_date, 1 id from dual union select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 2 from dual union select to_date('27.11.07 10:00', 'dd.mm.yyyy hh24:mi'), 1 from dual union select to_date('27.11.07 10:30', 'dd.mm.yyyy hh24:mi'), 2 from dual union select to_date('27.11.07 10:45', 'dd.mm.yyyy hh24:mi'), 1 from dual union select to_date('27.11.07 15:00', 'dd.mm.yyyy hh24:mi'), 2 from dual) select to_char(TargetMy_date,'dd.mm.yyyy hh24:mi') as TargetMy_date, to_char(ID2,'dd.mm.yyyy hh24:mi') as ID2 from (select my_date,id, max(decode(ID,1,my_date)) over () as TargetMy_date, Last_Value(case when ID = 2 then my_date end ignore nulls) over(order by my_date) as ID2 from td) where id = 1 and my_date = TargetMy_date;