thebigpeeler thebigpeeler - 3 months ago 14
MySQL Question

MySQL - select first occurences of a column value

Youve all been a big help in the past so I thought id give you another shot!

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?

Thankyou all in advance!

TheBigPeeler

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