Need distinct query by last date

[beanfair]

I have a mySQL table of product prices. The table has an autoincrement priceid, a product field, a price field and a date modified field. Each product may appear in the table multiple times, prices vary according to season. I want to query the table so that I have a recordset containing ONLY the latest price per product. If I have 10 products I only want 10 rows returned with the data from the row with the newest date in the datemod field.
I have tried lots of things and I usually get just 10 rows but it isn't selecting the correct row per product. I may get the June price for broccoli but the price for strawberries for September of 2004 instead of the latest price for strawberries. The prices are raised at different times for different products so I can't set the where to a certain range or date.
Any assistance is appreciated.
Thank you

[obsidian]

actually, you may need to do this with a couple queries. i'd recommend something like this:
[code]
<?php
$items = array();
$sql = mysql_query("SELECT DISTINCT product FROM myTable"); // get products
while ($item = mysql_fetch_array($sql)) {
  $x = array();
  $x['product'] = $item['product'];
  $sql2 = mysql_query("SELECT * FROM myTable WHERE product = '$item[product]' ORDER BY modDate DESC");
  $z = mysql_fetch_array($sql2); // most recent entry for this item
  $x['price'] = $z['price'];
  $x['modDate'] = $z['modDate'];
  $items[] = $x;
}

echo "<pre>\n";
print_r($items);
echo "</pre>\n";
?>
[/code]

now, you have an array $items that contains all your products with their most recent modDate and price.

hope this helps

[wickning1]

Single query solution (requires MySQL 4.1 or greater):

[code]SELECT t.* FROM myTable t INNER JOIN (
   SELECT product, MAX(date) as maxdate FROM myTable GROUP BY product
) m ON m.product=t.product AND m.maxdate=t.modDate[/code]

It will help to have an index on the `product` column.