Max()? Distinct?

[farmgeek]

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:

<?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";
?>

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

hope this helps

[Roger_Ramjet]

This you do with a GROUP BY clause

$sql = "SELECT product, price, MAX(mod_date) FROM prices GROUP BY product";

This will return a list of products with their last mod date and the corresponding price. You can also use the autoinc id field in the MAX() since the highest id is also the newest entry.

[Sxooter]

I'd say that first of all, your table structure is broken. The fact that the same product can appear multiple times means you've got denormalized data.

What you should have is a single table with all the products listed exactly once.

Then, a pricing table that can have references to those products multiple times. Then, getting the right answer is a single query.

People often think that denormalized tables are faster, and that is often true in some sense, since you don't have to maintain multiple tables and handle complex joins. However, the hoops you now have to jump through to get data that would just fall out with a single query requires more work.

If you need help redesigning your schema, let us know, I'd be more than happy to help you do that.