Here is the SQL
---------------

SELECT empid, jdate, job
FROM (
SELECT empid,
jdate,
job,
ROW_NUMBER()
OVER (PARTITION BY empid ORDER BY jdate DESC)
AS rnk
FROM (
SELECT empid,
jdate,
job,
LAG(job,1,job)
OVER(PARTITION BY empid ORDER BY jdate )
AS prev_job,
LAG(jdate,1,jdate)
OVER(PARTITION BY empid ORDER BY jdate )
AS prev_jdate
FROM TEST_EMP
)
WHERE job <> prev_job
)
WHERE rnk = 1
;


Here is the script to setup the test data
-----------------------------------------

-- DROP TABLE TEST_EMP;

CREATE TABLE TEST_EMP
(
empid NUMBER,
jdate DATE,
job NUMBER
);

INSERT INTO TEST_EMP VALUES (355,TO_DATE('8/9/2003',
'MM/DD/YYYY'),450);
INSERT INTO TEST_EMP VALUES (355,TO_DATE('9/18/2004',
'MM/DD/YYYY'),440);
INSERT INTO TEST_EMP VALUES (355,TO_DATE('2/7/2005',
'MM/DD/YYYY'),450);
INSERT INTO TEST_EMP VALUES (355,TO_DATE('8/18/2006',
'MM/DD/YYYY'),440);
INSERT INTO TEST_EMP VALUES
(355,TO_DATE('11/25/2006','MM/DD/YYYY'),440);

COMMIT;

-----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