Finding the maximum/minimum value

Solution

Use a subquery that finds the max() of the quantity you want to measure and compare against it ref.

Discussion

This is similar to totalling, but you use the min/max functions instead of sum. In addition, we need to compare individual records against this maximum value. So, to get the company name and order details of the largest order in the system:

SELECT c.co_name, p.pr_code, p.pr_desc, o.ord_qty
FROM companies c, products p, orders o
WHERE c.co_id=o.ord_company AND p.pr_code=o.ord_product
AND o.ord_qty = (SELECT max(ord_qty) FROM orders);
   

If there are two or more orders both have the same largest quantity, they will both be returned.