Jump to content

Need distinct query by last date


beanfair

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.