products - Stores product info.
prices - Stores prices of products. This will have multiple entries for each product, can say that I am storing price variations of the product. So everytime its changed a new record is added instead of updating the old one.
I have to fetch products which have been re-priced, meaning get the products with latest price and their second last variation also.
So I have to display like:
So long as the prices table contains the date that the new price was added I would assume that getting the current price would be a matter of using group wise maximum logic. Though getting the old price would be a little bit more tricky. The best thing to do would to add a column to the prices table that acts as a flag for the old price. Otherwise, it would be tricky in SQL alone to derive the previous price. Though one could argue at that point it would be nice to have two flags and update them with application logic. One flag for the current price and another for the previous price. Than it would be a simple mater of joining against the price table price with a condition against the flags for the current price and old price. An alternative to that would be to group by product and use group_concat ordering the prices by date as a delimited string. Than in the application language extract the current and previous price.
select * from prices order by pric_timedate DESC LIMIT 1 // Gets me latest
select * from prices order by pric_timedate DESC LIMIT 1,1 // Gets me second latest
Yes it does not do that. Actually i came to know that only old / new was required so instead of adding variations i added another field of old price to table and now it stores all in 1 record hence no need for the variations.