Here is the corrected SQL. The first one did not
give the latest promotion date based on a job code.


SELECT b.empid,
b.jdate,
b.job
FROM (
SELECT a.empid,
a.jdate,
a.job,
ROW_NUMBER()
OVER (PARTITION BY empid,job ORDER BY jdate DESC )
promo_rnk
FROM (
SELECT empid,
jdate,
job,
LEAD(job,1,job)
OVER(PARTITION BY empid ORDER BY jdate DESC )
prev_job
FROM TEST_EMP
) a
WHERE job <> prev_job
) b
WHERE job = 450
AND promo_rnk = 1
;


-----Original Message-----
From: Jeff Chirco
[mailto:oracledba-ezmlmshield-x13527297.[Email address protected]
Sent: Wednesday, May 16, 2007 7:30 PM
To: LazyDBA Discussion
Subject: sql help

Here is my dilemma. I have the table like below



EMPID DATE JOB

355 8/9/2003 450

355 9/18/2004 440

355 2/7/2005 450

355 8/18/2006 440

355 11/25/2006 440



What I need to get is somebody's last promotion date. So if I want to
see when this person was last promoted to job 440 I want to see
8/18/2006, not 11/25/2006 or 9/19/2004. Or if I want to see when this
person was last promoted to job 450 I would expect to see 2/7/2005. I
can't figure out how to query this data.

Thanks for any help.





Jeff





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page