0

I am using MySQL db and I have two tables, the first one is like this:

Product
GTIN | Name | Dosage | Quantity
111    AAA     10         500
222    BBB     35         199
333    CCC     15         33

and the second table,

Product_price
ID | GTIN | product_price | product_date
1    111      15             2020-11-23 10:30:00
1    111      17             2021-07-15 08:05:17
1    222      35             2019-01-03 00:00:00
1    111      19             2022-01-09 11:35:00
1    333      65             2011-11-11 00:00:00
1    222      45             2021-11-23 00:00:00

Can someone help me to get the desired result? I want to get the latest price for each product based on gtin. The result would look like this,

Result
GTIN | Name | Dosage | Quantity | Price
111    AAA     10         500      19
222    BBB     35         199      45
333    CCC     15         33       65
ZAJ
  • 793
  • 3
  • 23
  • 50
  • Please show us your best attempt. – RiggsFolly Jan 10 '22 at 09:54
  • which db version are you use? – Rahul Biswas Jan 10 '22 at 09:56
  • >>which db version are you use? MySQL 8.0 – ZAJ Jan 10 '22 at 10:03
  • @astentx I had a look at it. In my case I am using inner join between two tables and I was struggling – ZAJ Jan 10 '22 at 10:05
  • @astentx Those links which you added are not similar to my question an anway. Firstly I am using two tables which need inner join and secondly I have to compare dates!! – ZAJ Jan 10 '22 at 10:20
  • @ZAJ Two tables can be represented as one via join, that's why it doesn't matter. And the datatype of *last record* doesn't matter also, because anything that can have an order (i.e. that has `>=` comparison defined for the datatype) is processed in the same way. See [Total order](https://en.m.wikipedia.org/wiki/Total_order) – astentx Jan 10 '22 at 10:50

1 Answers1

1
SELECT p.GTIN, p.Name, p.Dosage, P.Quantity, pp.product_price
FROM Product p INNER JOIN
     Product_price pp
     ON p.GTIN = pp.GTIN
WHERE pp.date = (SELECT MAX(p2.date)
                FROM Product_price p2
                WHERE p2.GTIN = pp.GTIN
               );
Ritik Banger
  • 2,107
  • 5
  • 25
  • Thanks for your comment. Your query worked as expected!!. Thanks a lot. If I want to filter based on GTIN, how will the query look it. – ZAJ Jan 10 '22 at 10:17
  • Just to confirm, I just added gtin to the where clause like so, `SELECT p.GTIN, pp.price_date, pp.price FROM Product p INNER JOIN product_price pp ON p.GTIN = pp.GTIN WHERE pp.price_date = (SELECT MAX(p2.price_date) FROM Product_price p2 WHERE p2.GTIN = pp.GTIN ) and p.gtin = 111;` – ZAJ Jan 10 '22 at 10:23
  • 1
    SELECT p.GTIN, p.Name, p.Dosage, P.Quantity, pp.product_price FROM Product p INNER JOIN Product_price pp ON p.GTIN = pp.GTIN WHERE pp.date = (SELECT MAX(p2.date) FROM Product_price p2 WHERE p2.GTIN = pp.GTIN ) and p.GTIN=111; – Ritik Banger Jan 10 '22 at 10:37
  • I'm trying to implement this solution, thanks. How do I display the max date in the result set too? – Rotimi Jun 15 '23 at 10:53
  • SELECT p.GTIN, p.Name, p.Dosage, p.Quantity, pp.product_price, pp.product_date FROM Product p INNER JOIN Product_price pp ON p.GTIN = pp.GTIN WHERE pp.product_date = ( SELECT MAX(p2.product_date) FROM Product_price p2 WHERE p2.GTIN = pp.GTIN ); @Rotimi – Ritik Banger Jun 16 '23 at 10:14