SQL for Max Effective Dated Row from JOB table

SQL for max effective dated row from JOB table.

SELECT *
FROM PS_JOB J
WHERE J.EFFDT = (SELECT MAX (J_ED.EFFDT)
                 FROM PS_JOB J_ED
                 WHERE J_ED.EMPLID = J.EMPLID
                 AND J_ED.EMPL_RCD = J.EMPL_RCD
                 AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ = (SELECT MAX (J_ES.EFFSEQ)
                FROM PS_JOB J_ES
                WHERE J_ES.EMPLID = J.EMPLID
                AND J_ES.EMPL_RCD = J.EMPL_RCD
                AND J_ES.EFFDT = J.EFFDT)
ORDER BY J.EMPLID, J.EMPL_RCD, J.EFFDT, J.EFFSEQ;

If that returns multiple rows for a specific employee, that means he/she has additional assignments.
Use this SQL instead.

SELECT *
FROM PS_JOB J, PS_PER_ORG_INST POI
WHERE J.EMPLID = POI.EMPLID
AND J.EMPL_RCD = POI.ORG_INSTANCE_ERN
AND J.EFFDT = (SELECT MAX (J_ED.EFFDT)
                 FROM PS_JOB J_ED
                 WHERE J_ED.EMPLID = J.EMPLID
                 AND J_ED.EMPL_RCD = J.EMPL_RCD
                 AND J_ED.EFFDT <= SYSDATE)
AND J.EFFSEQ = (SELECT MAX (J_ES.EFFSEQ)
                FROM PS_JOB J_ES
                WHERE J_ES.EMPLID = J.EMPLID
                AND J_ES.EMPL_RCD = J.EMPL_RCD
                AND J_ES.EFFDT = J.EFFDT)
ORDER BY J.EMPLID, J.EMPL_RCD, J.EFFDT, J.EFFSEQ;