select the latest balance for each product based on store (SQL)

Question

I'm trying to get latest balance for each product based on storeid . But I don know why get result duplicate stock name sasi.

Sql Query

select stockname, balance,updatedat,storename,s1.productid,s1.storeid
from stockmovement s1
inner join (select storeid, productid, max(updatedat) as maxdate
            from stockmovement
            group by storeid,productid) s2
on s2.storeid = s1.storeid and s2.maxdate = s1.updatedat

Answer 1

Try below query using row_number():

select stockname, balance, bupdatedat, storename,productid,storeid from 
(
  select stockname, balance, bupdatedat, storename,productid,storeid, row_number() over 
  (partition by storeid,productid order by bupdatedat date desc) rn
)t where rn=1