thebigpeeler thebigpeeler - 3 months ago 13
MySQL Question

select smallest occurences of a column value

I'm struggling to get the following from the following table:

Original:

+--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | gala | 2.99 |
| apple | gala | 3.45 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+------------+-------+


What i really want:

+--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
+--------+------------+-------+


So i basically want to be able to select all the apple types, but the lowest of each price.

How can I achieve this in using mysql queries?

Answer
SELECT type, variety, MIN(price) AS price
FROM yourTable
WHERE type = 'apple'
GROUP BY type, variety