Abu,

Nice query although slightly flawed if you switch the tot_wgt around where so dept 4 = 80 and dept 5 = 50, you get the wrong result. however with a slight tweak, it will work, simply switch the order by dept to order by tot_wgt.

with SCC_CTE(vend_i, dept, tot_wgt)

as (select 1, 1, 75 union all

select 2, 2, 40 union all

select 3, 4, 80 union all

select 3, 5, 50)

select vend_i, dept from

(select ROW_NUMBER() over (Partition by vend_i order by tot_wgt desc) as theRank, vend_i, dept

from SCC_CTE) as a

where theRank = 1