Help with query

Hi all,

I am trying to populate a Datagrid with one SQL query but I can't get it to work the way I want. I have a table where I enter prices of articles, but not just the latest price, I also leave old prices inside. This way I can generate history of price changes.
This DB table consists of the following fields:

ID
Item_ID
Vendor_ID
Price
Date

When I try to pull a list of vendors that offer a certain article and the latest price, I always get all the prices for all the dates.
Here is the query:

select id, Vendor_ID, Price from shop_item_prices where Item__ID=1 order by Price asc

So could someone please tell me what do I have to do to get only 1 record per vendor with the latest price?

Thank you.

SELECT sip.ID,sip.VendorID,sip.Price

FROM Shop_item_prices sip

JOIN (SELECT item_id,vendor_id,MAX(Date) AS mdate FROM shop_item_prices GROUP BY Item_id,vendor_id) t1 ON sip.item_id=t1.item_id AND sip.vendor_id=t1.vendor_id AND sip.Date=t1.mdate

WHER Item_ID=1

|||

Great, it works :)

Thank you.