is there a sql query I want to get last job_id with the earliest start date

is there a sql query I want to get last job_id with the earliest start date.

I have a table named employee

employee_id job_id start_date  salary changed_on
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

expected results

employee_id job_id start_date 
123         bbb    2020-08-10
456         zzz    2020-08-13
789         ccc    2020-08-16

Answer

First, use FIRST_VALUE() window function to get the latest job_id for each employee_id and then use conditional aggregation to get the earliest start_date for this job_id:

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

See the demo.