choosing the proper row

I havetwo tables as follows:

SQL> desc project

 Name                            Null?    Type


 ------------------------------- -------- ----

 PROJ_ID                         NOT NULL NUMBER
 PROJ_NAMEL                               VARCHAR2(150)
 PROJ_NAMES                               VARCHAR2(150)
 PROJ_TYPE                                NUMBER(5)
 PROJ_COST                                VARCHAR2(150)
 PROJ_DATES                               VARCHAR2(500)
 PROJ_SUP                                 NUMBER
 GUID_COM                                 VARCHAR2(100)
 COWORKERS                                VARCHAR2(200)
 PROJ_DESC                                VARCHAR2(2000)
 OTHER_DOC                                VARCHAR2(2500)
 CURRENCY                                 NUMBER(5)
 STARTDATE                                VARCHAR2(10)
 ENDDATE                                  VARCHAR2(10)

SQL> desc t_phase
 Name                            Null?    Type


 ------------------------------- -------- ----

 NAME                                     NUMBER
 GOAL                                     VARCHAR2(1000)
 DEADLINE                                 VARCHAR2(10)
 START_DATE                               VARCHAR2(10)
 STATUS                                   VARCHAR2(15)
 PHASEID                         NOT NULL NUMBER(5)
 PROJ_ID                                  NUMBER(5)
 ENDDATE                                  VARCHAR2(10)

I join them by proj_id.
One record from project has several records in t_phase. I want to select one joined record from project and t_phase, according to the value of the STATUS firld in t_phase. The thing is that all the records that belong to project may have the same STATUS values. And in that case i want to select the one that that has the highest value of a number. Or instead, and I guess would be better, if I would put the string value in place of status field value in the resultset.

select name,...,status or 'finished' from t_phase....

or

where g.status='dgd' g.proj_id(+)=p.proj_id if null then'finished'

This is obviously a crap, but how can i do it? Received on Wed Nov 28 2001 - 12:09:29 CST