Jump to content

[SOLVED] Select row that has a unique value and max timestamp.


daydreamer

Recommended Posts

Hi, I have a table like this:

 

---Prices---

Price    Product ID    Supplier ID    Timestamp

44      1                2               

45      1                2

40      1                3

 

 

I am trying to select the latest price for each supplier for a given product ID.

 

This is what I have:

$result = mysql_query("SELECT * FROM prices WHERE product_id='$id' ORDER BY price desc")or die(mysql_error());

 

I have tried a few things like group by, and distinct but cannot get the exact data set I need.

 

How would I do this?

To get the actual row (so that you would get the correct information from other columns in it, like the price or an id) holding a specific value (max, min...) for a group, see this link -

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

 

Hi

 

To bring them all back, do a SELECT to bring back the latest timestamp for each produce, grouped by product id and supplier id, then JOIN that to the original table on the product id, supplier id and timestamp to get the rest of the fields (such as the actual price).

 

Something like this:-

 

SELECT a.Price, a.product_id, a.supplier_id, b.MaxTimeStamp
FROM Prices a
JOIN (SELECT product_id, supplier_id, Max(Timestamp) AS MaxTimeStamp
FROM Prices
GROUP BY product_id, supplier_id) b
ON a.product_id . b.product_id
AND a.supplier_id = b.supplier_id
AND a.Timestamp = b.MaxTimeStamp

 

You can then narrow this down to a particular product id with a WHERE clause (either at the end, or within the subselect).

 

All the best

 

Keith

Thanks for the link PFMaBiSmAd, did look at that page wasnt sure if I was able to use that method.

 

SELECT a.Price, a.product_id, a.supplier_id, b.MaxTimeStamp
FROM Prices a
JOIN (SELECT product_id, supplier_id, Max(Timestamp) AS MaxTimeStamp
FROM Prices
GROUP BY product_id, supplier_id) b
ON a.product_id . b.product_id
AND a.supplier_id = b.supplier_id
AND a.Timestamp = b.MaxTimeStamp

 

Thanks alot this worked a treat

Archived

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

×
×
  • 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.