Skip to Main Content
Forums

SQL & PL/SQL

Announcement

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

displaying based on latest date and second latest date

407048Sep 17 2007 — edited Sep 18 2007
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
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 
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

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 
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
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 16 2007
Added on Sep 17 2007
4 comments
10,043 views