Re: select a row

From: Simon Redmond <simon_at_sibass.com>
Date: 29 Nov 2001 02:52:14 -0800
Message-ID: <746cebd2.0111290252.2a4775bd_at_posting.google.com>


longinus <longinus_at_zeus.polsl.gliwice.pl> wrote in message news:<3C0533A4.A8458468_at_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

I'm a little confused here, you are basing you're selection on the value of STATUS yet you mention if all fields have the same value for STATUS you base it on the highest value of a number... what field is that? Also you've got your date fields defined as VARCHAR's... any reason? Received on Thu Nov 29 2001 - 11:52:14 CET

Original text of this message