How to fetch only the latest ordered items from the list by eliminating duplicate items SQL-CodePudding

How to write SQL query for fetch only the latest ordered items and remove the duplicated items. here BOOK Ordered on 2 days so fetch only latest ordered recorded.

expected result

so here two rows removed and pick the latest ordered items . PEN and BOOK ordered two times but only took the latest order.

CodePudding user response：

Here is full working

CodePudding user response：

Try this

select * 
from( select *, ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY DATE DESC) as rn
      from have 
    ) as a
where rn = 1
;