Sorry it may have been a bit confusing. The last promotion date is
8/18/2006 when they went from job 450 to job 440. The other two more
recent records are not really promotions, just other transactions that
happened on the table. I know I know, the table was poorly designed a
long time ago, but I am stuck with it, at least for now. One day it
will change.
Thanks for your help.

-----Original Message-----
From: Parveen Kumar
[mailto:oracledba-ezmlmshield-x84356942.[Email address protected]
Sent: Wednesday, May 16, 2007 5:14 PM
To: LazyDBA Discussion
Subject: Re: sql help

I am not bit clear about the result you want then

if you have another record for job 440 then for empid 355 last promoted
date should be 11/25/2006 and these queries will return the same value
.So what actually you want , you want 8/18/2006 in all cases (these
queries will return second highest dates i.e last promoted for an
employee) , tell me if i am wrong here

Parveen


Jeff Chirco wrote:
> I couldn't figure out how to get #1 to work.
> But #2 is close but imagine if I had another record for job 440. Like
so
>
> 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
>
> 355 2/7/2007 440
>
> -----Original Message-----
> From: Parveen Kumar
> [mailto:oracledba-ezmlmshield-x65798821.[Email address protected]
> Sent: Wednesday, May 16, 2007 4:58 PM
> To: LazyDBA Discussion
> Subject: Re: sql help
>
> why don't you try these
>
> 1)
>
> select level,max(sal) from emp
> where level=2
> connect by prior sal>sal
> group by level;
>
> here connect by is special construction in Oracle to view data in tree

> manner and can take the values any values from any level..
>
> best of luck
>
>
> 2) select max(sal) from where sal != (select max(sal) from emp)
>
> Parveen
>
> Chris wrote:
>> I was a bit hasty
>> I think this is it....
>>
>> select *
>> from prod..ps_job j
>> where j.empid = 355
>> and job = 440
>> and j.date = (select max(x.date)
>> from prod..ps_job x
>> where x.empid = j.empid
>> and x.date <= (select max(y.date)
>> from prod..ps_job y
>> where y.job = 450))
>>
>>
>> -----Original Message-----
>> From: Jeff Chirco
>> [mailto:oracledba-ezmlmshield-x77832440.[Email address protected]
>> Sent: Wednesday, May 16, 2007 4:44 PM
>> To: LazyDBA Discussion
>> Subject: RE: sql help
>>
>>
>> No that would return the last record for job 440 which is 11/25/2006.
> I
>> want to see 8/18/2006.
>> Thanks
>>
>> Jeff
>>
>> -----Original Message-----
>> From: Chris
>> [mailto:oracledba-ezmlmshield-x96819660.[Email address protected]
>> Sent: Wednesday, May 16, 2007 4:39 PM
>> To: LazyDBA Discussion
>> Subject: RE: sql help
>>
>> Jeff-
>> Are you using ADP software?
>> Try something like this...
>>
>> select *
>> from prod..ps_job j
>> where j.empid = 355
>> and j.date = (select max(x.date)
>> from prod..ps_job x
>> where x.empid = j.empid)
>>
>> -----Original Message-----
>> From: Jeff Chirco
>> [mailto:oracledba-ezmlmshield-x13527297.[Email address protected]
>> Sent: Wednesday, May 16, 2007 4: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
>>
>>
>>
>>
>
>
>
> ---------------------------------------------------------------------
> 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
>
>
>
>
> ---------------------------------------------------------------------
> 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
>
>



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