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

[daydreamer]

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?

[JustLikeIcarus]

Ummm.. Have you tried..?

 

select supplier_id, price, max(timestamp)
from prices
where product_id = ?
group by supplier_id, price

[PFMaBiSmAd]

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

 

[kickstart]

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

[daydreamer]

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