By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (employee_id INTEGER, job_id VARCHAR(3), start_date DATE, salary INTEGER, changed_on DATETIME);
INSERT INTO tablename (employee_id, job_id, start_date, salary, changed_on) VALUES
('123', 'aaa', '2020-08-09', '40000', '2020-08-09'),
('123', 'bbb', '2020-08-10', '50000', '2020-08-10'),
('123', 'bbb', '2020-08-11', '60000', '2020-08-11'),
('456', 'xxx', '2020-08-12', '40000', '2020-08-12'),
('456', 'zzz', '2020-08-13', '50000', '2020-08-13'),
('456', 'zzz', '2020-08-14', '60000', '2020-08-14'),
('789', 'aaa', '2020-08-15', '70000', '2020-08-15'),
('789', 'ccc', '2020-08-16', '80000', '2020-08-16');
SELECT t.employee_id,
MAX(t.last_job_id) job_id,
MIN(CASE WHEN t.job_id = t.last_job_id THEN t.start_date END) start_date
FROM (
SELECT employee_id, job_id, start_date,
FIRST_VALUE(job_id) OVER (PARTITION BY employee_id ORDER BY start_date DESC) last_job_id
FROM tablename
) t
GROUP BY t.employee_id
| employee_id | job_id | start_date |
|---|---|---|
| 123 | bbb | 2020-08-10 |
| 456 | zzz | 2020-08-13 |
| 789 | ccc | 2020-08-16 |