At 07:03 AM 11/03/2004 +0000, you wrote:

>I have this statement that gives me the largest amount of sales for
>the customer with id 205. The result is correct.
>
>select "Cust ID", "Deliver Date", "Grand Total"
>from "DR" m
>where m."Cust ID" = 205 and not exists (select *
> from "DR" d
> where d."Cust ID" = 205 and m."Grand Total" < d."Grand Total")
>
>
>However if I want the reverse, the smallest amount of sales for this
>customer 205 my fB didnt stop running even for such a long time, and
>it hangs I think.
>
>
>select "Cust ID", "Deliver Date", "Grand Total"
>from "DR" m
>where m."Cust ID" = 205 and not exists (select *
> from "DR" d
> where d."Cust ID" = 205 and m."Grand Total" > d."Grand Total")
>
>is there something wrong with this script? or is there a right way of
>doing this?

Yes, this should do them both:

select m."Cust ID", m."Deliver Date", m."Grand Total"
from "DR" m
where m."Cust ID" = 205
and m."Grand Total" =
(select MAX(d."Grand Total")
from "DR" d
where d."Cust ID" = 205)

select m."Cust ID", m."Deliver Date", m."Grand Total"
from "DR" m
where m."Cust ID" = 205
and m."Grand Total" =
(select MIN(d."Grand Total")
from "DR" d
where d."Cust ID" = 205)

/hb

>Thank.
>
>Regards,
>James
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>