with TDAPay as (
select '201004' ym, '0001' EmpId, 50000 Pay union all
select '201004' ym, '0002' EmpId, 70000 Pay union all
select '201004' ym, '0003' EmpId, 30000 Pay union all
select '201004' ym, '0004' EmpId, 40000 Pay union all
select '201004' ym, '0005' EmpId, 50000 Pay union all
select '201004' ym, '0006' EmpId, 90000 Pay union all
select '201004' ym, '0007' EmpId, 20000 Pay union all
select '201004' ym, '0008' EmpId, 60000 Pay union all
select '201004' ym, '0009' EmpId, 80000 Pay union all
select '201004' ym, '0010' EmpId, 10000 Pay
),   TDAEmpMaster as (
select '0001' EmpId, '01' DeptCd, 'Kang Min Su' EmpNm union all
select '0002' EmpId, '01' DeptCd, 'Kim Min Su' EmpNm union all
select '0003' EmpId, '02' DeptCd, 'Jang Min Su' EmpNm union all
select '0004' EmpId, '02' DeptCd, 'Park Min Su' EmpNm union all
select '0005' EmpId, '03' DeptCd, 'Bak Min Su' EmpNm union all
select '0006' EmpId, '03' DeptCd, 'Lee Min Su' EmpNm union all
select '0007' EmpId, '04' DeptCd, 'Yi Min Su' EmpNm union all
select '0008' EmpId, '04' DeptCd, 'Jung Min Su' EmpNm union all
select '0009' EmpId, '01' DeptCd, 'Choi Min Su' EmpNm union all
select '0010' EmpId, '02' DeptCd, 'Cho Min Su' EmpNm
),   TDADept  as (
select '01' DeptCd, 'Dept01' DeptNm union all
select '02' DeptCd, 'Dept02' DeptNm union all
select '03' DeptCd, 'Dept03' DeptNm union all
select '04' DeptCd, 'Dept04' DeptNm
)
select a.DeptCd
     , a.DeptNm
     , a.DeptPay
     , a.EmpId
     , a.EmpNm
     , a.Pay
  from (
         select a.DeptCd
              , a.DeptNm
              , a.DeptPay
              , a.EmpId
              , a.EmpNm
              , a.Pay
              , row_number()over(order by a.DeptPay desc, a.pay desc) rn
           from (
                  select b.DeptCd
                       , c.DeptNm
                       , a.EmpId
                       , b.EmpNm
                       , a.Pay
                       , sum(a.pay)over(partition by b.DeptCd) DeptPay
                    from TDAPay       a
                       , TDAEmpMaster b
                       , TDADept      c
                   where a.ym     = '201004'
                     and a.EmpId  = b.EmpId
                     and b.DeptCd = c.DeptCd
                ) a
       ) a
 where a.rn = 1

윗분은 오라클 기준으로 하셨나보네요 ^^;

전 그냥 간단하게 MS-SQL버젼으로 바꿔봅니다..ㅎ

select top(1)
   b.DeptCd
       , c.DeptNm
       , a.EmpId
       , b.EmpNm
       , a.Pay
       , sum(a.Pay)over(partition by b.DeptCd) DeptPay
    from TDAPay       a
       , TDAEmpMaster b
       , TDADept      c
   where a.ym     = '201004'
     and a.EmpId  = b.EmpId
     and b.DeptCd = c.DeptCd
order by DeptPay desc, a.Pay desc