SQL: Find Maximum and Minimum Values

Table Fruit

id fruit    cultivar       price
1 Apple FUJI          0.99
2 Apple limbertwig  1.50
3 Apple Macintosh  2.00
4 Orange valencia    1.29
5 Orange Sunkis      1.99
6 Pear bradford       2.99
7 Pear Bartlett         2.45
8 cherry Chelan       3.89
9 Cherry Bang         1.99

1. Find the most expensive (maximum price) fruit of each type. List them in a form of

fruit cultivar price
Apple Macintosh 2.00
cherry Chelan 3.89
Orange Sunkist 1.99
Pear Bradford 2.99


1) Use max() function

Most databases, MySql, Oracle, MS SQL Server, etc, has build in function to get the max value.

a) We can use the max() function to get
the maximum price of each fruit and then use a self-join to get the row.

select f.fruit, f.cultivar, f.price
from (
select fruit, max(price) as maxprice
from fruit group by fruit
) as f2 inner join fruit as f on f.fruit = f2.fruit and f.price = f2.maxprice;


b) We can also use the max() function with a correlated subquery.
This can be much less efficient.

select fruit, cultivar, price
from fruit f
where price = (select max(price) from fruit as f2 where f2.fruit = f.fruit);

2) Without using the max() function

We can also use only self-join to get the result.

SELECT f.fruit, f.cultivar, f.price
FROM fruit f
LEFT JOIN fruit f2 ON (f.fruit = f2.fruit
AND f.price <= f2.price)
GROUP BY f.fruit, f.price
 HAVING COUNT(*) = 1 ;

The following query is for demonstration only. It is an inefficient query. Also, it will not produce correct result if there are more than one record with the same value (price).

select fruit, cultivar, price
from fruit f
where price = (select price from fruit as f2 where f2.fruit = f.fruit order by price desc limit 1);

2. Find the Minimum Price of each fruit

1) Use min() function a) using a self-join to get the row.
select f.fruit, f.cultivar, f.price
  from ( select fruit, min(price) as minprice from fruit group by fruit ) as f2
 inner join fruit as f
 on f.fruit = f2.fruit and f.price = f2.minprice;

b) using a correlated subquery.

select fruit, cultivar, price
 from fruit f
where price = (select min(price) from fruit as f2 where f2.fruit = f.fruit);

2) Without using the min() function

SELECT f.fruit, f.cultivar, f.price
FROM fruit f
LEFT JOIN fruit f2 ON (f.fruit = f2.fruit AND f.price = f2.price)
GROUP BY f.fruit, f.price
HAVING COUNT(*) = 1;