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



Oracle LazyDBA home page