Getting max date from min date

Dip

Hello.

We have a problem with getting max values out of min values.

Example (table):
date.....................type
27.11.07 06:00........1
27.11.07 10:00........2
27.11.07 10:00........1
27.11.07 10:30........2
27.11.07 10:45........1
27.11.07 15:00........2

Max value is max time with type 1. In this case, max is at time 10:45.
Min value is max time with type 2, which must be smaller than max time with type 1.

So we have 10:45, which is good. And we need 10:30.
But we get two values for min. That is 10:00 and 10:30.

How can we solve that?

Thanks.

105967

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> 

21205

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

 ) 

;

Dip

I need to get both: 10:30 and 10:45.

jeneesh

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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Dip

So i need to use two select statements.

I knew that i could do that.
But i thought, there is some shorter way to get what i want (because i have long SQL statement).


Thanks.

Aketi Jyuuzou

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;