CODEWITHSUNDEEP
mysqltimestamp
user1406177
user1406177

Reputation: 1370

Selecting multiple max timestamps from database

I have a table with multiple products. For each product, I am collecting an history of prices. Thus I have four columns:

Until now, I updated all prices at the same time. This lead to an easy query: In a subquery, I filtered the max timestamp and then got all prices and products.

Today I changed my update policy: I now update prices for products asynchronously. This means, when I use my old query, I only get a product-price-pair with the maximum timestamp.

How do I get the most current price/the maximum timestamp for every product in one query?

Upvotes: 0

Views: 220

Answers (2)

Pierre
Pierre

Reputation: 884

You could use group by on a result that was sorted by timestamp. Not sure if it works:

select * from (
  select * from products 
    order by timestamp desc
) as products_temp
group by product_id;

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

If the timestamp field is of timestamp data type, then on every field update of a record, this timestamp field can get updated automatically. For that to happen your ts field should have been defined like in the following table pseudo example. 

CREATE TABLE t1 (
  product ...
  price ...
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

And when you update only other fields like product, or price, ts fields get updated with current timestamp. Then your old query should be returning the latest updated records too.

Upvotes: 1

Related Questions