Re: How to write simple select clause for the following problem

Hello!
I think that the following query will return the required date:

select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable;

However, if you need the name column too, you may try

select * from mytable where
 date1=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable)

   or
 date2=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable)

   or
 date3=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable);

This is quite inefficient compared to selecting the greatest date to a PL/SQL variable and looking for that value;

Be careful, because this query may return multiple rows if the same greatest value can be found in different rows. If you desperately need a single row containing the last date, maybe this query helps you out.

select * from mytable where rowid=(
select min(ROWID) from mytable where
 date1=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable)

   or
 date2=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable)

   or
 date3=(select greatest(max(DATE1),max(DATE2),max(DATE3)) from mytable)
);

HTH, regards:
  Flatline

oliveryao2000_at_yahoo.com (Oliver Yao) wrote in message news:<d617ee1.0211151840.372be2bf_at_posting.google.com>...
> Assume a table MYTABLE looks like the following:
>
> MYTABLE:
>
> NAME DATE1 DATE2 DATE3
> X 01/01/02 01/01/02 02/01/02
> Y 02/04/02 02/05/02 01/30/02
> Z 02/20/02 02/05/02 11/14/02
>
> The problem is to find the last date (i.e. 11/14/02) in all three column
> DATA1, DATA2 and DATA3. Thanks.
Received on Sat Nov 16 2002 - 13:19:58 CET