Max year of every employee salary

Create table employee(eid int,esal money, ename varchar(20))

insert into employee values(1,'raju')

insert into employee values(2,'ramu')

Create table salary(eid int,esal money,dateyear int)

insert into salary values(1,3000,2009)

insert into salary values(1,6000,2010)

insert into salary values(1,3000,2011)

insert into salary values(1,300,2020)

insert into salary values(2,5000,2009)

insert into salary values(2,9000,2010)

Select eid,esal,dateyear from salary

/*           eid          esal                        dateyear

1              3000.00                2009

1              6000.00                2010

1              3000.00                2011

2              5000.00                2009

2              9000.00                2010*/

select t.eid,ename,esal,dateyear from employee as e inner join (

select s.eid,s.esal,s.dateyear from salary as s inner join

(select eid,max(dateyear) as dateyear from salary group by eid) as a

on a.eid=s.eid and s.dateyear=a.dateyear) t on  e.eid=t.eid

/*--Result:

2              ramu     9000.00 2010

1              raju        300.00                  2020*/