--- In firebird-support@yahoogroups.com, "james_027" 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?

Hi James, I like your query, but in order to help you it would be
benefitial if you mentioned the plan chosen and some statistics. I
suspect you need to modify your indexes a bit or change to

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" or 2=0))

Though it all depends on lots of things, e.g. how many records exists
for "Cust ID" 205.

HTH,
Set