Jump to content

Archived

This topic is now archived and is closed to further replies.

beanfair

Need distinct query by last date

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.