Forum Stats

  • 3,734,031 Users
  • 2,246,861 Discussions
  • 7,857,002 Comments

Discussions

Getting max date from min date

Dip
Dip Member Posts: 659 Bronze Badge
edited November 2007 in SQL & PL/SQL
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.

Comments

  • 105967
    105967 Member Posts: 1,027
    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
    21205 Member Posts: 6,168 Gold Trophy
    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
    Dip Member Posts: 659 Bronze Badge
    I need to get both: 10:30 and 10:45.
  • jeneesh
    jeneesh Member Posts: 7,168
    edited November 2007
    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
    Dip Member Posts: 659 Bronze Badge
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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;
This discussion has been closed.