is there a sql query I want to get last job_id with the earliest start date.
I have a table named employee
x
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
Advertisement
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.