Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Unanswered: Joining to most recent rows on or prior to a date

    I have a table of goods and a table of prices, e.g.:

    goods (id, name, quantity):
    1, 'Apples', 100
    2, 'Pears', 50

    prices (id, good_id, date, price)
    1, 1, '20014-01-01', 13.5
    2, 2, '20014-01-01', 17.0
    3, 1, '20014-01-10', 12.5
    4, 2, '20014-01-11', 16.0
    5, 1, '20014-02-03', 12.75
    6, 2, '20014-02-04', 16.25

    I want to be able to find the value of the goods on a given date based on the most recent price data on or before that date but I can't for the life of me figure out the join that would be required. Can anyone point me in the right direction please?

  2. #2
    Join Date
    Dec 2014
    Posts
    3

    Filtering dates to get recent prices...

    Hi Boxersoft,

    This is what I came up with to look for the price of apples on or before 2014-01-31. There may be a more efficient way, but I am learning...

    SELECT g.name, p.price, p.date
    FROM prices as p, goods as g
    WHERE g.id=p.good_id
    AND good_id=1
    AND DATE <= '2014-01-31'
    ORDER BY DATE DESC;

    If you don't need to see the name of the goods, get rid of the join:

    SELECT good_id, price, date
    FROM prices
    WHERE good_id=1
    AND DATE <= '2014-01-31'
    ORDER BY DATE DESC;

    Please let me know if this helps! =D

    mysql> select g.name, p.price, p.date from prices AS p, goods AS g where g.id=p.good_id AND good_id=1 AND DATE <= '2014-01-31' order by DATE DESC;
    +--------+-------+------------+
    | name | price | date |
    +--------+-------+------------+
    | apples | 12.5 | 2014-01-10 |
    | apples | 13.5 | 2014-01-01 |
    +--------+-------+------------+
    2 rows in set (0.00 sec)

    mysql> SELECT good_id, date, price FROM prices WHERE good_id=1 AND DATE <= '2014-01-31' ORDER BY DATE DESC;
    +---------+------------+-------+
    | good_id | date | price |
    +---------+------------+-------+
    | 1 | 2014-01-10 | 12.5 |
    | 1 | 2014-01-01 | 13.5 |
    +---------+------------+-------+
    2 rows in set (0.00 sec)

    Also... you can add LIMIT 1 to the end to just get the most recent date and price.

    EDIT: I just read that you wanted the value (meaning the price times the quantity?) I added one more thing to include this:

    mysql> select g.name, p.price, p.date, p.price*g.quantity AS value from prices AS p, goods AS g where g.id=p.good_id AND good_id=1 AND DATE <= '2014-01-31' order by DATE DESC LIMIT 1;
    +--------+-------+------------+--------------------+
    | name | price | date | value|
    +--------+-------+------------+--------------------+
    | apples | 12.5 | 2014-01-10 | 1250 |
    +--------+-------+------------+--------------------+
    1 row in set (0.00 sec)



    Quote Originally Posted by Boxersoft View Post
    I have a table of goods and a table of prices, e.g.:

    goods (id, name, quantity):
    1, 'Apples', 100
    2, 'Pears', 50

    prices (id, good_id, date, price)
    1, 1, '20014-01-01', 13.5
    2, 2, '20014-01-01', 17.0
    3, 1, '20014-01-10', 12.5
    4, 2, '20014-01-11', 16.0
    5, 1, '20014-02-03', 12.75
    6, 2, '20014-02-04', 16.25

    I want to be able to find the value of the goods on a given date based on the most recent price data on or before that date but I can't for the life of me figure out the join that would be required. Can anyone point me in the right direction please?
    Last edited by LearningMySQL; 12-30-14 at 04:01. Reason: Added result sets and LIMIT option

  3. #3
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by LearningMySQL View Post
    Hi Boxersoft,

    SELECT good_id, price, date
    FROM prices
    WHERE good_id=1
    AND DATE <= '2014-01-31'
    ORDER BY DATE DESC;

    Please let me know if this helps! =D
    Thanks for the input but it doesn't solve the problem. My fault for failing to make it clearer.

    This query will bring back all prices on or before the target date for one goods item. Sorting and limiting can reduce that to the most recent one, as you rightly say, but only for a single item. I want to haul back just the most recent price rows for each of the goods items in a single query, and that's what had me stumped. I was thrashing away with GROUP BY on the assumption that had to be the way forward, but the solution I eventually found used a completely different approach involving an OUTER JOIN, which may explain why I had difficulty finding it before I posted here.

    The OUTER JOIN approach strikes me as unintuitive but it's rather clever and it works for me.

Tags for this Thread

Posting Permissions

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