Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,867 Comments

Discussions

displaying based on latest date and second latest date

407048
407048 Member Posts: 894
edited Sep 18, 2007 6:40AM in SQL & PL/SQL
Oracle 10.2.0.3 on solaris :
I have a history table(say ORDERS_HIST) with 3 fields : CODE, DATE and VALUE
The data is as follows :
CODE DATE VALUE
ABD 09/15/2007 23
ABD 09/16/2007 54
ABD 09/17/2007 22
KLO 01/01/2007 72
KLO 04/20/2007 89
KLO 04/21/2007 91

Each code can have multiple records
ABD code value was changed from 23 to 54 and then to 22 on the given dates.

I needed to display in my result set :

ABD 22 54
KLO 91 89

In other words, the most recent value and the second recent value(based on date) is what I am trying to display
Can this be done using a SELECT and if so, how?

Comments

  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    Try this:
    with t as (
      select 'ABD' code, to_date('09/15/2007','MM/DD/YYYY') Thedate, 23 value from dual union all
      select 'ABD', to_date('09/16/2007','MM/DD/YYYY'), 54 from dual union all
      select 'ABD', to_date('09/17/2007','MM/DD/YYYY'), 22 from dual union all
      select 'KLO', to_date('01/01/2007','MM/DD/YYYY'), 72 from dual union all
      select 'KLO', to_date('04/20/2007','MM/DD/YYYY'), 89 from dual union all
      select 'KLO', to_date('04/21/2007','MM/DD/YYYY'), 91 from dual
    )
    select code, 
           min(value) keep (dense_rank first order by thedate desc) first,
           min(value2) keep (dense_rank first order by thedate desc) second
      from (
        select code, 
               thedate, 
               value,
               lead(value,1) over (partition by code order by thedate desc) value2
          from t
      )
     group by code;
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 17, 2007 6:12PM
    create table Work as
    select 'ABD' as CODE,to_date('09/15/2007','mm/dd/yyyy') as DATEX,23 as VALUE from dual
    union select 'ABD',to_date('09/16/2007','mm/dd/yyyy'),54 from dual
    union select 'ABD',to_date('09/17/2007','mm/dd/yyyy'),22 from dual
    union select 'KLO',to_date('01/01/2007','mm/dd/yyyy'),72 from dual
    union select 'KLO',to_date('04/20/2007','mm/dd/yyyy'),89 from dual
    union select 'KLO',to_date('04/21/2007','mm/dd/yyyy'),91 from dual
    union select 'XYZ',to_date('01/11/2007','mm/dd/yyyy'),30 from dual
    union select 'XYZ',to_date('01/22/2007','mm/dd/yyyy'),20 from dual
    union select 'XYZ',to_date('01/30/2007','mm/dd/yyyy'),10 from dual
    union select 'ZZZ',to_date('11/21/2007','mm/dd/yyyy'),66 from dual
    union select 'ZZZ',to_date('11/23/2007','mm/dd/yyyy'),22 from dual
    union select 'ZZZ',to_date('11/25/2007','mm/dd/yyyy'),33 from dual;
    select CODE,
    max(decode(Rank,1,VALUE)) as "latest date",
    max(decode(Rank,2,VALUE)) as "second latest date"
    from (select CODE,VALUE,
    Row_Number() over(partition by CODE order by DATEX desc) as Rank
    from Work)
    where Rank <= 2
    group by CODE
    order by CODE;
    1. in InlineView ,I get Rank.
    2. using Pivot Query.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Sep 18, 2007 6:40AM
    or
    select distinct CODE,
    Last_Value(VALUE) over(partition by CODE order by DATEX
    Rows between Unbounded Preceding and Unbounded Following) as "latest date",
    Last_Value(LagVALUE) over(partition by CODE order by DATEX
    Rows between Unbounded Preceding and Unbounded Following) as "second latest date"
    from (select CODE,DATEX,VALUE,
    Lag(VALUE) over(partition by CODE order by DATEX) as LagVALUE
    from Work)
    order by CODE;
    or
    select CODE,
    VALUE as "latest date",
    LagVALUE as "second latest date"
    from (select CODE,DATEX,VALUE,
    Lag(VALUE) over(partition by CODE order by DATEX) as LagVALUE,
    max(DATEX) over(partition by CODE) as MaxDATEX
    from Work)
    where DATEX=MaxDATEX
    order by CODE;
  • 578885
    578885 Member Posts: 93
    SQL> ed
    Wrote file afiedt.buf

    1 with t as
    2 (
    3 select 'ABD' code, to_date('09/15/2007','MM/DD/YYYY') Thedate, 23 value from dual union all
    4 select 'ABD', to_date('09/16/2007','MM/DD/YYYY'), 54 from dual union all
    5 select 'ABD', to_date('09/17/2007','MM/DD/YYYY'), 22 from dual union all
    6 select 'KLO', to_date('01/01/2007','MM/DD/YYYY'), 72 from dual union all
    7 select 'KLO', to_date('04/20/2007','MM/DD/YYYY'), 89 from dual union all
    8 select 'KLO', to_date('04/21/2007','MM/DD/YYYY'), 91 from dual
    9 )
    10 select c,
    11 (select value from t where t.code = t2.c and t.thedate = t2.d) value1,
    12 (select value from t where t.code = t2.c and t.thedate = t2.d1) value2
    13 from
    14 (
    15 select c, d, (select max(thedate) from t where t.code = t1.c and t.thedate < t1.d) d1
    16 from
    17 (
    18 select code c, max(thedate) d from t
    19 group by code
    20 ) t1
    21* ) t2
    22 /

    C VALUE1 VALUE2
    --- ---------- ----------
    ABD 22 54
    KLO 91 89
This discussion has been closed.