Re: selecting minimum date

[Mike Olson <msolson@FASTENAL.COM>]

Is the main select from the same table as the sub select?  If so, then
try something like this:

from table t1
where....
and t1.approval_date = (select min(approval_date) from table t2 where
t2.ingred_no = t1.ingred_no)

Mike Olson


> -----Original Message-----
> From: Jennifer Wagner 301-827-5467 FAX 301-594-6463
> [SMTP:WAGNERJ@CDER.FDA.GOV]
> Sent: Thursday, August 12, 1999 8:30 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      selecting minimum date
>
> 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"