update ITEM 
set ITEM_POPULAR_RANK = B.ida
from
(
select row_number() over (order by cnt desc, max(ORDER_DATE) desc, it.ITEM_CODE desc) as ida
, it.ITEM_CODE , cnt, max(ORDER_DATE) as dt from ITEM as it
   left join ORDERS_DTL as od on od.ITEM_CODE  = it.ITEM_CODE 
   left join ORDERS as o on o.ORDER_NO = od.ORDER_NO
   inner join (
   select it.ITEM_CODE , ifnull(sum(ORDER_QTY),0) as cnt from ITEM as it
   left join ORDERS_DTL as od on od.ITEM_CODE  = it.ITEM_CODE 
   left join ORDERS as o on o.ORDER_NO = od.ORDER_NO

   group by it.ITEM_CODE
   order by cnt desc
) as temp on temp.ITEM_CODE = it.ITEM_CODE

   group by it.ITEM_CODE
   order by cnt desc,dt desc, it.ITEM_CODE desc) as B
   where ITEM.ITEM_CODE = B.ITEM_CODE