Re: SQL max value

From: GA <remove_to_mail>
Date: Wed, 26 Feb 2003 23:17:01 -0000
Message-ID: <3e5d4b10$0$6301$cc9e4d1f@news.dial.pipex.com>

Ken,

Are you using enterprise or personal editions, if so analytical functions can be used to remove "(select max(dt_run) from active where a.case = case"

for example

SELECT *
FROM (SELECT a.case, a.dt_run, row_number() over (partition by a.case order by a.dt_run desc) rn

            FROM active a, work w
        WHERE a.case = w.case)

where rn = 1

using the Scot demo tables:

select *
from ( select d.deptno

, d.dname
, e.hiredate
, row_number() over(partition by d.deptno order by e.hiredate desc ) rn

From emp e, dept d
where e.deptno = d.deptno)
where rn = 1

With standard edition this is only avaliable in version 9+

Regards

Graham

"Ken Chesak" <datavector_at_hotmail.com> wrote in message news:3f2f39c4.0302261359.159dac84_at_posting.google.com...
> I need to join 2 tables, work and active by case and then find the
> max(dt_run)in the active table. I came up with the following SQL, but
> is there a better way? Tables will be large and the actual SQL is
> much more involved. Oracle 8.1.7.2.0.
>
> select a.case, a.dt_run
> from active a, work w
> where a.case = w.case
> group by a.case, a.dt_run
> having a.dt_run = (select max(dt_run) from active where a.case = case
> group by case)
>
> results
> CASE DT_RUN
> 1 15-JAN-03
> 2 15-DEC-02
>
> create table active ( dt_run date, case number);
> create table work ( dt_run date, case number);
>
> Work table data
> DT_RUN CASE
> 15-FEB-03 1
> 15-NOV-02 2
>
> Active table data
> DT_RUN CASE
> 15-JAN-03 1
> 15-DEC-02 1
> 15-NOV-02 1
> 15-NOV-02 2
> 15-DEC-02 2
Received on Wed Feb 26 2003 - 17:17:01 CST