Re: selecting minimum date

["Salem, Mark" <msalem@MSA.COM>]

Your first scenario should work if it is in a date format.  I am using
Oracle 7.3 and the following works:

select key_col, date_col
from table_1
where parent_key_col = 302
and date_col = (select  min(date_col)
                from    table_1
                where
                        parent_key_col = 302);


The results are:

KEY_COL         DATE_COL
---------               -----------
      147       05-APR-1999
      199       05-APR-1999
      200       05-APR-1999
      201       05-APR-1999
      202       05-APR-1999

> -----Original Message-----
> From: Jennifer Wagner 301-827-5467 FAX 301-594-6463
> Sent: Thursday, August 12, 1999 9:30 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      selecting minimum date
> Sensitivity:  Confidential
>
> Howdy all,
>
> You were such a great help last time, I thought I'd give it a shot again
> (someday I hope to be able to return the help, but you all know about
> 100% more than I do currently!!).
>
> Okay, now I'm working on a program that will output the first
> introduction of an ingredient on the market.  As you can guess, an
> ingredient can be in a zillion different products.  So, what I want is
> the first approval date for that ingredient.
>
> I tried doing it like this:
>
> where....
> and approval_date = (select min(approval_date) from table where
> ingred_no = &ingred)
>
> &ingred, of course, was defined earlier in the main select statement.
> Okay, that returned zilch.  I was told that I needed to transform the
> approval date to number.  So, I tried this (because I was told that it
> should be a Julienne number):
>
> select....
>
> approval_date
> to_char(approval_date) &app
>     let #app = &app
>
> where...
> approval_date = (select min(#app)...)
>
> didn't work.
>
> I even tried:
>
> where
> approval_date = (select min(to_num(to_char(approval_date,'J')))
> and got a "invalid column" error for "to_num"
>
> Any suggestions??
>
> Thanks for your help!!!
>
> Jenny
>
> PS to Wayne -- I hope this doesn't cause a confidential notice.  I
> switched it to "non-restricted"