Just trying to pull records from a single table with multple change reocrds per item. I want the last change. Query pulls all records.
Query
Select productkey, productcostcode, NewCostPrice, max(StartDate) from IC_ProductCostPriceChanges
where productkey = '4080'
group by productkey, productcostcode, NewCostPrice
Results
productkey productcostcode NewCostPrice Date
4080 7 0.0001 8/4/2015 0:00
4080 7 10.549 8/13/2015 0:00
Have tried in Access as well same results.
Thanks
Try this:
select productkey
,productcostcode
,newcostprice
,startdate
from (select productkey
,productcostcode
,newcostprice
,startdate
,row_number() over(partition by productkey
,productcostcode
order by startdate desc
) as rn
from ic_productcostpricechanges
where productkey='4080'
) as a
where rn=1
2 Likes
Bless You! it worked.
Can you tell me why I have to do that and can't just use Max?
You can use "max", but I like the "row_number" method, because it helps you deal with duplicates.
Say you have a duplicate - with the "max" we can't distinguish the entries - with "row_number" we can.
Also you original query didn't work because you grouped by newcostprice.
Using "max" would look something like:
select b.productkey
,b.productcostcode
,b.newcostprice
,b.startdate
from (select productkey
,productcostcode
,max(startdate) as startdate
from ic_productcostpricechanges
where productkey='4080'
) as a
inner join ic_productcostpricechanges as b
on b.productkey=a.productkey
and b.productcostcode=a.productcostcode
and b.startdate=a.startdate
1 Like