Here are two options. You should run each of these with autotrace on
as well as timing on. You should also make sure that you are getting
the right information. In your query you only wanted emplid, but you
can just as easily get the other fields.
You also did not explain why you did the joins that you did and whether
or not you need to do the joins that way. It could be that both queries
give you the same information, in that case the first query should be faster.
Code:
SELECT a.EMPLID, b.max_effdt, b.max_effseq, b.max_empl_rcd
FROM ps_job a
INNER JOIN ( SELECT emplid, max(effdt) max_effdt,
max(effseq) max_effseq, max(empl_rcd) max_empl_rcd
FROM ps_job ) b
ON a.emplid = b.emplid
AND a.effdt = b.effdt
AND a.effseq = b.effseq
ORDER BY 1;
Code:
SELECT a.EMPLID, b.max_effdt, c.max_effseq, d.max_empl_rcd
FROM ps_job a
INNER JOIN ( SELECT emplid, max(effdt) max_effdt,
FROM ps_job ) b
ON a.emplid = b.emplid
INNER JOIN ( SELECT emplid, max(effseq) max_effseq,
FROM ps_job ) c
ON a.emplid = b.emplid
AND a.effdt = b.effdt
INNER JOIN ( SELECT emplid, max(empl_rcd) max_empl_rcd
FROM ps_job ) d
ON a.emplid = b.emplid
AND a.effdt = b.effdt
AND a.effseq = b.effseq
ORDER BY 1;