analytic function instead of correlated subquery 1

[emblem]

Hi All,

This is a question about recoding pervasive SQL in a PeopleSoft HRMS application to take advantage of Oracle's analytic functions. The JOB table contains employees' history, and has the essential structure

*emplid
*effdt
*effseq
 deptid
 jobcode
 (hundreds of other fields)

An essential task is to prepare lists of employees' current jobs as of a given date. So you want the max(effdt) by employee where effdt =< sysdate or other date input, and effseq is the tiebreaker when there are multiple rows with the same emplid-effdt.

The traditional syntax is as follows:

select emplid, deptid, effdt, effseq
from ps_job j
where 
    effdt in (select max(effdt) from ps_job j1 where j1.emplid =j.emplid and j1.effdt <= (to_date('10-MAR-1997'))) and
    effseq in (select max(effseq) from ps_job j2 where j2.emplid =j. emplid and j2.effdt = j.effdt) 
and emplid in ('10000','10001','10007')	
order by emplid

Here is sample data
emplid dept effdt effseq
----- ----- -------- -
10000 DSSCC 1/2/1997 0
10000 DSSCC 1/1/1997 0
10001 PUR1 10/25/1996 0
10001 PUR1 10/9/1996 0
10007 ECIS 5/1/1997 0
10007 HRR_K 3/10/1997 0
10007 ECIS 3/10/1997 1
10007 HRR_K 1/1/1997 0
10007 KHRIX 1/1/1997 1
10007 HRR_K 10/1/1996 0

The query above gives this (corect) output
10000 DSSCC 1/2/1997 0
10001 PUR1 10/25/1996 0
10007 ECIS 3/10/1997 1

I'm trying to get the same results with something like

select  emplid, deptid,
(first_value(effdt) over (partition by emplid order by effdt desc))
from ps_job WHERE EMPLID in ('10000','10001','10007')

but this gives me all rows, with the highest effdt per emplid in each row. I just want 3 rows, without using a subquery of any type. Is this possible? I'm also not sure how to use effseq as a tiebreaker.

Many thanks for your suggestions.

 

[Dagon]

Your code will just give you the first value of the effdt for every row. You need something like:

select * from 
(select  emplid, deptid,
(rank() over (partition by emplid order by effdt desc, effseq desc)) as ranking
from ps_job WHERE EMPLID in ('10000','10001','10007'))
where ranking = 1

 

[emblem]

Thank you! This works very well.

 

[djbjr]

Thanks Dagon for that Valueble Post.