Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table emp_sal (
    empid int,
    change_date date,
    salary int
);
insert into emp_sal
values
    (1, date'2023-01-01', 1000),
    (1, date'2023-02-01', 1400),
    (1, date'2023-03-01', 1450),
    (1, date'2023-04-01', 1500),
    (2, date'2023-11-01', 2500),
    (2, date'2023-12-01', 2400),
    (2, date'2023-12-15', 2200),
    (3, date'2023-05-01',  500),
    (3, date'2023-06-01',  640),
    (4, date'2023-10-01', 3000);
10 rows affected

select s.empid, s.change_date, s.salary
from (
    select
        e.*,
        row_number() over (partition by e.empid order by e.change_date desc) as rn,
        count(*) over (partition by e.empid) as cnt
    from emp_sal e
) s
where s.rn = least(3, s.cnt)
order by s.empid;


EMPID
CHANGE_DATE
SALARY


1
01-FEB-23
1400

2
01-NOV-23
2500

3
01-MAY-23
500

4
01-OCT-23
3000