[SOLVED] How do I select only the lowest price for each unique ID?

[daydreamer]

Hi,

 

I have a table:

 

---tblPrices---

ID, Price, Product ID

1    22    106

2    25    106

3    19    106

4    88    107

5    77    107

 

I want to select only the lowest price for each unique product ID in one SQL call, how do I do it?!

 

Thanks.

 

[corbin]

You could use a group by clause along with an order.

 

 

SELECT ID, MIN(minPrice) FROM tblPrices GROUP BY ID

[Alex]

I think you mean:

 

SELECT ID, MIN(Price) FROM tblPrices GROUP BY Product ID

[PFMaBiSmAd]

And if you want the actual rows with the minimum in each group (not just the minimum value in a group) you will need to use one of the methods at the following link (alter the code to use the MIN() function as needed) - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

[daydreamer]

Thanks for the solutions, my actual SQL had a left join in it so the normal

SELECT ID, MIN(Price) FROM tblPrices GROUP BY Product ID

 

wasnt working.

 

And if you want the actual rows with the minimum in each group (not just the minimum value in a group) you will need to use one of the methods at the following link (alter the code to use the MIN() function as needed) - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

 

This sorted it, Cheers