Jump to content


Photo

Need distinct query by last date


  • Please log in to reply
2 replies to this topic

#1 beanfair

beanfair
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 27 March 2006 - 02:31 PM

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

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 27 March 2006 - 02:37 PM

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
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 27 March 2006 - 03:24 PM

Single query solution (requires MySQL 4.1 or greater):

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

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users