SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How to get prices from vartiations ?

    Hi,

    I have 2 tables i.e:

    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:

    Prod Name, Latest Price, Old Price

    How to achieve this ?

    Thanks.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.
    Hi,

    I just did the following and it worked:

    Code:
    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
    Seems to be working fine.

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    Seems to be working fine.
    sure, but that's the latest two rows overall, not the latest two per product
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    557
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    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.

    thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •