Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL max value

SQL max value

From: Ken Chesak <datavector_at_hotmail.com>
Date: 26 Feb 2003 13:59:31 -0800
Message-ID: <3f2f39c4.0302261359.159dac84@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 - 15:59:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US