I am writing the sql query to get the latest supervisor and their job

Question

I am writing the sql query to get the latest supervisor and their job but problem I am facing is that there are multiple supervisor are coming up in the query with multiple jobs for their for the employee. there should be only one supervisor with one job.

I can get the correct supervisor_id and their job IN THE FIRST ROW OF THE BELOW QUERY. using

SELECT SUPERVISOR_ID,JOB_ID 
FROM PER_ALL_ASSIGNMENTS_F 
WHERE PERSON_ID = 24387 
ORDER BY EFFECTIVE_START_DATE DESC;

This code retrieves the Leaver, their supervisor and the supervisor Job.

select 
DISTINCT
PO.SEGMENT1,
HR.NAME,
PAPF.FULL_NAME AS LEAVER_FULL_NAME,
PAPF.PERSON_ID LEAVER_ID,
PAAF.SUPERVISOR_ID,
PAPF2.FULL_NAME AS SUPERVISOR,
PJ.Name JOB_NAME
FROM po_headers_all PO,
HR_OPERATING_UNITS HR,
po_distributions_all PD,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_PEOPLE_F PAPF2,
PER_ALL_ASSIGNMENTS_F PAAF,
per_person_types ppt ,
PER_JOBS PJ
WHERE PD.PO_HEADER_ID=PO.PO_HEADER_ID
AND PO.CLOSED_CODE = 'OPEN'
AND PO.TYPE_LOOKUP_CODE LIKE 'STANDARD'
AND PAPF.PERSON_ID= PD.DELIVER_TO_PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PAPF2.PERSON_ID =PAAF.SUPERVISOR_ID
AND HR.ORGANIZATION_ID = PO.ORG_ID
AND PJ.JOB_ID=PAAF.JOB_ID
AND PJ.NAME NOT IN ('Director','Vice President',' CEO','CFO','Executive VP','GlobalOperations EVP','Mfg VP','Sourcing and Proc VP','Sr. Vice President')
AND PJ.NAME NOT LIKE 'Senior VP%GM'
--AND   PAPF.EFFECTIVE_START_DATE BETWEEN :P_DATE_FROM AND P_DATE_TO
--AND   PAAF.EFFECTIVE_START_DATE BETWEEN :P_DATE_FROM AND P_DATE_TO
--AND PAPF.EFFECTIVE_START_DATE BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
--AND PAAF.EFFECTIVE_START_DATE BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD') 
AND   papf.person_type_id           = ppt.person_type_id
AND   UPPER(PPT.SYSTEM_PERSON_TYPE) = UPPER('EX_EMP')
 and PO.SEGMENT1 in ('2044611')
ORDER BY PAPF2.FULL_NAME

I need to get only one Supervisor and their current supervisor job. its fetching multiple supervisor and with multiple jobs.

Answer 1

Use the ROW_NUMBER() function to assign row numbers, followed by retrieving the first and last users based on those assigned row numbers (start date)