CODEWITHSUNDEEP
mysqlsql
inMILD
inMILD

Reputation: 198

MySQL lowest and latest price per product

I have 3 tables

    price_history (Primary: id)
    +----+------------+------+---------+-------+------------+
    | id | id_product | sku  | id_shop | price |  date_add  |
    +----+------------+------+---------+-------+------------+

    | 1  | 11         | 101  | 1001    | 10    | 2017-07-01 |
    | 2  | 12         | 101  | 1002    | 15    | 2017-07-01 |
    | 3  | 13         | 101  | 1003    | 20    | 2017-07-01 |
    | 4  | 11         | 101  | 1001    | 11    | 2017-07-02 | <-- lowest latest

    | 5  | 14         | 102  | 1001    | 45    | 2017-07-01 |
    | 6  | 15         | 102  | 1002    | 45    | 2017-07-01 |
    | 7  | 16         | 102  | 1003    | 45    | 2017-07-01 | <-- shop 1003 is the lowest,
    latest among shops and should not be display, because the shop monitored is 1001
    | 8  | 15         | 102  | 1002    | 60    | 2017-07-02 | 
    | 9  | 14         | 102  | 1001    | 55    | 2017-07-02 | 

    |10  | 17         | 103  | 1001    | 90    | 2017-07-01 |
    |11  | 18         | 103  | 1002    | 90    | 2017-07-01 |
    |12  | 19         | 103  | 1003    | 90    | 2017-07-01 |
    |13  | 17         | 103  | 1001    | 100   | 2017-07-02 | <-- lowest latest 
    |14  | 18         | 103  | 1002    | 100   | 2017-07-02 |
    |15  | 19         | 103  | 1003    | 100   | 2017-07-02 |
    +----+------------+------+---------+-------+------------+

    product (primary: id_product)
    +------------+---- ---+-----+--------------+---------+
    | id_product | active | sku | product_name | id_shop |
    +------------+--------+-----+--------------+---------+
    | 11         | 1      | 101 | Red          | 1001    |
    | 12         | 1      | 101 | A bit red    | 1002    |
    | 13         | 1      | 101 | Very red0    | 1003    |
    | 14         | 1      | 102 | Blue         | 1001    |
    | 15         | 1      | 102 | A bit blue   | 1002    |
    | 16         | 1      | 102 | Very blue    | 1003    |
    | 17         | 1      | 103 | Green        | 1001    |
    | 18         | 1      | 103 | A bit green  | 1002    |
    | 19         | 1      | 103 | Very green   | 1003    |
    | 20         | 0      | 104 | Discontinued | 1001    |
    | 21         | 0      | 104 | Out of stock | 1002    |
    | 22         | 0      | 104 | Varnish      | 1003    |
    +------------+--------+-----+--------------+---------+

    shop (primary: id_shop)
    +---------+--------+
    | id_shop | name   |
    +---------+--------+
    | 1001    | Shop A |
    | 1002    | Shop B |
    | 1003    | SHop C |
    +---------+--------+

Every shop has a different product name, but has international number (sku).

ID shop to monitor: 1001 (Shop A)

Already search in stackoverflow, but i cannot get the right one. How can i get the lowest and latest price per sku. Im still newbie that not understand subselect

This what i have done so far

    SELECT pph.id, s.name, h2.price, h2.sku
    FROM (
        SELECT MAX(h.id) max_id, MIN(h.price) min_price, h.id, h.id_shop
        FROM price h
        GROUP BY sku
    ) pph 
    LEFT JOIN price h2 ON (h2.id = max_id)
    LEFT JOIN shop s ON (s.id_shop = pph.id_shop)

Then in the last i need only show the monitored shop, 'Shop A'

Expected result:

    +----+------------+------+---------+-------+
    | id | id_product | sku  | id_shop | price | 
    +----+------------+------+---------+-------+
    | 4  | 11         | 101  | 1001    | 11    |
    |13  | 17         | 103  | 1001    | 100   |
    +----+------------+------+---------+-------+

Lowest price for sku 102 is shop 1003, so we dont need to show them.

JSFiddle http://sqlfiddle.com/#!9/ba3a2

Thank you

Need help, still not found the solution

Upvotes: 0

Views: 79

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can simply achieve this using a correlated subquery with limit clause:

select *
from price_history h
where id_shop = 1001
and id = (select id
          from price_history p
          where h.sku = p.sku
          and h.id_shop = p.id_shop
          order by date_add desc, price asc
          limit 1
          );

The above is simple solution but may suffer from performance issues for larger dataset.

You can use the below join based solution then:

select *
from price_history
join (
    select sku, date_add, min(price) as price
    from price_history
    join (
        select sku, max(date_add) as date_add
        from price_history
        where id_shop = 1001
        group by sku
    ) t using (sku, date_add)
    where id_shop = 1001
    group by sku, date_add
) t using (sku, date_add, price)
where id_shop = 1001

or

select *
from price_history
join (
    select sku, date_add, id_shop, min(price) as price
    from price_history
    join (
        select sku, id_shop, max(date_add) as date_add
        from price_history
        where id_shop = 1001
        group by sku, id_shop
    ) t using (sku, date_add, id_shop)
    group by sku, date_add, id_shop
) t using (sku, date_add, price, id_shop);

Demo

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

This will give you max date_add per sku for a shop

SELECT sku, MAX(date_add)
FROM price_history
WHERE id_shop = 1001
GROUP BY sku;

You can then wrap this into another query and JOIN with price_history again, to get the result, e.g.:

SELECT ph.id, ph.id_product, ph.sku, ph.id_shop, ph.price
FROM proce_history ph JOIN (
 SELECT sku, MAX(date_add) AS `date_add`
 FROM price_history
 WHERE id_shop = 1001
 GROUP BY sku
) a ON ph.sku = a.sku AND ph.date_add = a.date_add;

Upvotes: 0

Related Questions