TSQL JOIN Clarification

Question

Suppose i have two tables

declare @emp table
(
  EmpID int,
  EmpName varchar(10)

)

declare @Remu table
(
   EmpID int,
   Sal Decimal(10,2),
   PaidYear varchar(10)
)

I want maximum salary grouped on PaidYear (With Ties)

Expected OUTPUT

EmpID EmpName PaidYear Sal 
1     Jon     2001     2000
2     Smith   2001     2000

3     Nash   2003      4000     
4     Hoge   2005      5000
5     Peter  2005      5000

I have an issue when using Join

select e.EmpID,e.EmpName,r.Sal,r.PaidYear from @emp e
inner join
(select max(Sal) as Sal,PaidYear from @Remu group by PaidYear)r
on e.EmpID=???

when i select EmpID in

select max(Sal) as Sal,PaidYear from @Remu group by PaidYear

i have to Group by PaidYear and EmpID,which won't give the desired result as i expected.

How to solve this.I want a query which should be compatible with SQL Server 2000.

Answer 1

select e.EmpID,e.EmpName,r.Sal,r.PaidYear
from @emp e inner join @Remu r on e.EmpId = r.EmpId
where r.sal in (select max(sal) from @remu group by paidyear)

Answer 2

Each year needs to determine a single max salary specific to that year.

select e.EmpID
    , e.EmpName
    , r.Sal
    , r.PaidYear 
from @emp as e 
inner join @Remu as r 
on e.EmpId = r.EmpId 
where r.sal = (select max(sal) 
               from @remu 
               where paidyear = r.PaidYear ' makes it year specific
              ) 

Data To Test:

declare @emp table 
( 
  EmpID int, 
  EmpName varchar(10) 

) 

declare @Remu table 
( 
   EmpID int, 
   Sal Decimal(10,2), 
   PaidYear varchar(10) 
) 

insert into @emp (EmpID, EmpName)
values(1, 'Jon')
insert into @emp (EmpID, EmpName)
values(2, 'Smith')
insert into @emp (EmpID, EmpName)
values(3, 'Nash')
insert into @emp (EmpID, EmpName)
values(4, 'Hoge')
insert into @emp (EmpID, EmpName)
values(5, 'Peter')

Insert into @Remu (EmpID, Sal, PaidYear)
values(1, 2000, '2001')

Insert into @Remu (EmpID, Sal, PaidYear)
values(2, 4999, '2001') 

Insert into @Remu (EmpID, Sal, PaidYear)
values(2, 8000, '2003') 

Insert into @Remu (EmpID, Sal, PaidYear)
values(3,4000, '2003')      

Insert into @Remu (EmpID, Sal, PaidYear)
values(4, 5000, '2005')      

Insert into @Remu (EmpID, Sal, PaidYear)
values(5, 4999, '2005')

Results:

EmpID   EmpName Sal    PaidYear
4           Hoge    5000.00    2005
2           Smith   8000.00    2003
2           Smith   4999.00    2001