select a row

From: Kock, Gerd <GKock_at_pdv-Online.de>
Date: Thu, 29 Nov 2001 08:10:40 +0100
Message-ID: <90333AAED533D411950600104B45E01904095BEF@pdv-exchng.pdv-online.de>

select proj.proj_namel, outer_phase.name, outer_phase.status from project proj, t_phase outer_phase
where proj.proj_id = outer_phase.proj_id and outer_phase.status = (select max(status)

                          from t_phase inner_phase
                          where inner_phase.proj_id =

outer_phase.proj_id);

Is this what you meant?

Gerd Kock
www.bits-on-the.net

-----Ursprüngliche Nachricht-----
Von: longinus [mailto:longinus_at_zeus.polsl.gliwice.pl] Bereitgestellt: Mittwoch, 28. November 2001 19:56 Bereitgestellt in: server
Unterhaltung: select a row
Betreff: select a 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 Received on Thu Nov 29 2001 - 01:10:40 CST