Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.

SELECT

 A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT

FROM    PS_JOB A, PS_JOB B

WHERE

 B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1

WHERE B.EMPLID = A_ED1.EMPLID

AND B.EMPL_RCD = A_ED1.EMPL_RCD

AND A_ED1.EFFDT < A.EFFDT)

AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1

WHERE B.EMPLID = A_ES1.EMPLID

   AND B.EMPL_RCD = A_ES1.EMPL_RCD                            

AND B.EFFDT = A_ES1.EFFDT)

AND A.EMPLID = B.EMPLID

  AND A.EMPLID='KA0002′

Is this the only way to get the results?????

Here is the Simple and Advanced way to achieve the same results without using Self Join

SELECT

A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT

FROM     PS_JOB A

WHERE      A.EMPLID='KA0002′

Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function

SELECT

A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT

FROM PS_JOB A

WHERE A.EMPLID='KA0002′

Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.