select a row

From: longinus <longinus_at_zeus.polsl.gliwice.pl>
Date: Wed, 28 Nov 2001 19:56:17 +0100
Message-ID: <3C053351.6B7A0434@zeus.polsl.gliwice.pl>

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 Received on Wed Nov 28 2001 - 12:56:17 CST