bharathimc02 (6/12/2013)
This is the query i was trying to do...i was trying to get vend_i and dept which has max(wgt) when grouped by vend_i and dept
select vend_i,dept,max(tot_wgt)
from
table1
group by vend_i,dept
The reason that this did not work is because it is already grouped by both vendor and department. Thus you will get all vendor and department combinations, and the max tot_wgt for each of them (and since I don't think there are duplicates with same vendor and dept, the max effectively does nothing).
What you need to do is first identify which rows have the highest tot_wgt for each vendor, and then find the dept for those rows.
One question not covered by your sample data, what happens when two or more depts within a vendor are TIED for the highest tot_wgt? Jason/Abu's solution would simply take one of these and ignore the others. You'll need a slightly different query otherwise.