Home » SQL & PL/SQL » SQL & PL/SQL » max dates
max dates [message #250676] Tue, 10 July 2007 15:50 Go to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
I have the table in the following data structure
RECORD_NO     EVENT_CODE     EFF_DATE       VALUE1    VALUE2
1                A           1/1/2007        20        100
1                A           2/1/2007        20        100
1                A           3/1/2007        10        100
2                A           1/1/2007        20        100
2                A           2/1/2007        20        100
2                B           2/1/2007        60        25
2                B           2/1/2007        20        100
3                B           2/1/2007        20        100
3                A           2/1/2007        80        50


Query should select record with a maximum effective date for each record number for event 'A'. How do we do this ? Is group by and max the only way..table has more than 2 million records and its slow...

RECORD_NO     EVENT_CODE     EFF_DATE       VALUE1    VALUE2
1                A           3/1/2007        10        100
2                A           2/1/2007        20        100
3                A           2/1/2007        80        50


Thank you
Re: max dates [message #250683 is a reply to message #250676] Tue, 10 July 2007 17:25 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Don't know whether it'll be faster, but this approach may be worth trying ...

>SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 10 18:22:30 2007

select deptno, ename, hiredate, sal
from scott.emp
order by deptno ;

    DEPTNO ENAME      HIREDATE          SAL
---------- ---------- ---------- ----------
        10 CLARK      1981-06-09       2450
        10 KING       1981-11-17       5000
        10 MILLER     1982-01-23       1300
        20 JONES      1981-04-02       2975
        20 FORD       1981-12-03       3000
        20 ADAMS      1987-05-23       1100
        20 SMITH      1980-12-17        800
        20 SCOTT      1987-04-19       3000
        30 WARD       1981-02-22       1250
        30 TURNER     1981-09-08       1500
        30 ALLEN      1981-02-20       1600
        30 JAMES      1981-12-03        950
        30 BLAKE      1981-05-01       2850
        30 MARTIN     1981-09-28       1250

14 rows selected.


select *
from
(
  select deptno, ename, hiredate, sal,
    dense_rank() over ( partition by deptno order by hiredate desc ) score
  from scott.emp
)
where score = 1
order by deptno ;

    DEPTNO ENAME      HIREDATE          SAL      SCORE
---------- ---------- ---------- ---------- ----------
        10 MILLER     1982-01-23       1300          1
        20 ADAMS      1987-05-23       1100          1
        30 JAMES      1981-12-03        950          1

3 rows selected.



--
Joe Fuda
SQL Snippets
Re: max dates [message #250701 is a reply to message #250676] Tue, 10 July 2007 23:24 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
SQL> with t as (select 1 RECORD_NO, 'A' EVENT_CODE, to_date('1/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  2             select 1 RECORD_NO, 'A' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  3             select 1 RECORD_NO, 'A' EVENT_CODE, to_date('3/1/2007','dd/mm/yyyy') EFF_DATE, 10 VALUE1, 100 VALUE2 from dual union all
  4             select 2 RECORD_NO, 'A' EVENT_CODE, to_date('1/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  5             select 2 RECORD_NO, 'A' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  6             select 2 RECORD_NO, 'B' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 60 VALUE1, 25 VALUE2 from dual union all
  7             select 2 RECORD_NO, 'B' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  8             select 3 RECORD_NO, 'B' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 20 VALUE1, 100 VALUE2 from dual union all
  9             select 3 RECORD_NO, 'A' EVENT_CODE, to_date('2/1/2007','dd/mm/yyyy') EFF_DATE, 80 VALUE1, 50 VALUE2 from dual)
 10             --
 11             select record_no,
 12                    event_code,
 13                    max(EFF_DATE) eff_date,
 14                    max(VALUE1) keep(dense_rank last order by eff_date) value1,
 15                    max(VALUE2) keep(dense_rank last order by eff_date) value2
 16               from t
 17              where event_code = 'A'
 18              group by record_no, event_code
 19  /

 RECORD_NO EVENT_CODE EFF_DATE        VALUE1     VALUE2
---------- ---------- ----------- ---------- ----------
         1 A          03.01.2007          10        100
         2 A          02.01.2007          20        100
         3 A          02.01.2007          80         50

SQL> 


but be careful if there more than one max eff_date.
Previous Topic: QUERY PROBLEMS: PLEASE HELP
Next Topic: create type
Goto Forum:
  


Current Time: Thu Dec 08 18:01:26 CST 2016

Total time taken to generate the page: 0.05256 seconds