# Thread: Min/max problem - Need help

1. Registered User
Join Date
Nov 2011
Posts
5

## Unanswered: Min/max problem - Need help

Hi,
I have a problem related to the function min and max. In this case Im working with 5 tables with this relation:

1. Customer(Name, address, KNR(PK)
2. Belong(KNR(FK), BENR(FK)
3. Order(order_sum, BENR(PK)
4. IN_Order(price, order_amount, order_date, BENR(FK), BANR(FK)
5. Product (Product_name, product_price, BANR(PK)

I want to answar this question:

- Which customer(s) (show the customer(s), have the highest order sum and which have the lowest order sum. I should also show the product in the order.

If I have been unclear so feel free to ask

/Martin

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
what have you tried so far?
where has that gone wrong?
what steps did you take to see if you could resolve the problem?

3. Registered User
Join Date
Nov 2011
Posts
5

My code:

Select name, order_sum, product_name from customer c, belong b, order o, in_order i, product p WHERE order_sum=(select min(order_sum), max(order_sum) from order) AND p.banr=i.banr AND i.benr=o.benr AND o.benr=b.benr AND b.knr=c.knr;

The result of the code above:
- operand should contain i column(s)

When I only use max() function i get the right result of max.
How should I use both max and min function in the code?

4. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
Try the following:

Select name, order_sum, product_name
from customer c,
belong b,
order o,
in_order i,
product p,
(select min(order_sum) minsum, max(order_sum) maxsum FROM order) x
WHERE (order_sum = x.minsum OR order_sum = x.maxsum)
AND p.banr=i.banr
AND i.benr=o.benr
AND o.benr=b.benr
AND b.knr=c.knr;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•