Max effdt in a sql statement

[jayjabour]

I am trying to select data from 3 tables to create one report. My environment is 9i. Here is the script I wrote:
select a.emplid "employee ID", b.oprdefndesc "Name" c.deptid "Cost Center", c.supivisor_id "Supivisor ID", b.oprdefndesc "Supervisor", a.monetary_amount "Amount"
From PS_ex_ee_sheet_vw A, PSOPRDEFN B, PS_JOB C
where a.emplid=b.emplid
and a.emplid=c.emplid
My problem is that the PS_job table has multiple row per emplid for each time the person gets a raise or something. So I want to take the information from the most current row in the PS_JOB table. There is a field in that table effdt. So I want the most current effdt. Any help would be greatly appreciated.

[DaPi]

In place of PS_JOB you could use a sub-query something like (untested):

(select * from 
 (select j.*,
         max(effdt) over (partition by emplid) mxdt
  from   PS_JOB j
 )
 where  effdt=mxdt
) C

This assumes that you only have one row per employee with a max(effdt).