-1

I am having a problem retrieving latest record with duplicate id.

product table

product id            prodoctcode
1                     po1
2                     po2
3                     po3
4                     po4
5                     po5
6                     po6
7                     po7

price table

priceid   product id              price         yearadded
1              1                     10            2018
2              1                     13            2019
3              2                     14            2015
4              2                     15            2016
5              3                     16            2019
6              4                     17            2017
7              4                     18            2015

I want this result where only the latest records are retrieved. 

product id            prodoctcode      price    yearadded
1                        po1              13        2019
2                        po2              15        2016
3                        po3              16        2019
4                        po4              17        2017

I tried select distinct(product id) but no luck

Shreyas
  • 999
  • 6
  • 19
Kikiloaw
  • 33
  • 5

1 Answers1

0

You need also a join with the subquery for max yeardded 

select 
    a.`product id`, a.prodoctcode, b.price, b.yearadded 
from  
    producttable a 
inner join 
    pricetable  b on a.`product id` = b.`product id`
inner join  
     (select `product id`, max(yearadded) year
      from pricetable 
      group by `product id`) on t.`product id` = b.`product id` and t.year = b.yearadded
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107