Thread: regarding sql query

i have id-primary key,jobNo-it can have repeated values,owner of job and start date fields in a table. i want to write query which will extract current owner of job ,jobno, with the date when the job was started for example
ID jobNo ownerofjob startdate
1 99 kiran 8-02-07
2 99 dondapati 09-03-07
3 99 sunil 12-03-07
4 1000 ask 14-03-07
i want result as 99,sunil and date 8-02-07(when the job started)
please help regarding this .

hi take this solution it will help u------------>




12:14:21 bhaskar>with t as
12:14:22 2 (select 1 as id, 99 as jobid, 'kiran' as owner, to_date('08-02-07','dd-mm-yy') as st
artdate from dual union all
12:14:22 3 select 2 as id, 99 as jobid, 'dondapati' as owner, to_date('09-03-07','dd-mm-yy') a
s startdate from dual union all
12:14:22 4 select 3 as id, 99 as jobid, 'sunil' as owner, to_date('12-03-07','dd-mm-yy') as st
artdate from dual union all
12:14:22 5 select 4 as id, 1000 as jobid, 'ask' as owner, to_date('14-03-07','dd-mm-yy') as s
tartdate from dual union all
12:14:22 6 select 5 as id, 1000 as jobid, 'sunil' as owner, to_date('13-03-07','dd-mm-yy') as
startdate from dual
12:14:22 7 )
12:14:22 8 select
12:14:22 9 distinct jobid, first_value(owner) over (partition by jobid order by startdate desc)
as last_owner,
12:14:22 10 first_value(startdate) over (partition by jobid order by startdate ) as first_s
tart
12:14:22 11 from t
12:14:22 12 order by jobid ;

JOBID LAST_OWNE FIRST_STA
---------- --------- ---------
99 sunil 08-FEB-07
1000 ask 13-MAR-07

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=5 Bytes=90
)

1 0 SORT (UNIQUE) (Cost=11 Card=5 Bytes=90)
2 1 WINDOW (SORT) (Cost=12 Card=5 Bytes=90)
3 2 WINDOW (SORT) (Cost=12 Card=5 Bytes=90)
4 3 VIEW (Cost=10 Card=5 Bytes=90)
5 4 UNION-ALL
6 5 FAST DUAL (Cost=2 Card=1)
7 5 FAST DUAL (Cost=2 Card=1)
8 5 FAST DUAL (Cost=2 Card=1)
9 5 FAST DUAL (Cost=2 Card=1)
10 5 FAST DUAL (Cost=2 Card=1)

Hi Suresh,

Try this query,
select j.job_id, j.ownerofjob, a.first_date
from jobs j,
(select job_id,max(startdate) last_date,min(startdate) first_date
from jobs j group by job_id) a
where j.job_id = a.job_id
and j.start_date = a.last_date;

The inline view in the from clause actually fetches actual start date and max start date for each job.

Hope this helps you.

Bhaski,
Your query looks really interesting. Could u please explain that query

bhaski please post only the code part using proper code tags.

Kindly do not post the execution plan.

Originally Posted by jamesravid

Hi Suresh,

Try this query,
select j.job_id, j.ownerofjob, a.first_date
from jobs j,
(select job_id,max(startdate) last_date,min(startdate) first_date
from jobs j group by job_id) a
where j.job_id = a.job_id
and j.start_date = a.last_date;

The inline view in the from clause actually fetches actual start date and max start date for each job.

Hope this helps you.

Bhaski,
Your query looks really interesting. Could u please explain that query

hi --

i just used analytic function first_value()
where first time i did partition by jobid(two jobid here 99 and 1000) order by startdate desc
which made a data group partitioned by jobid where startdate is by desc
and from this partition t took first value of owner by first_value(owner)
and similarly in second i created partition and took the value

sorry

next time i will do it properly.


sorry again

any way you are doing great work .
Youa re an asset to the oracle forum.
keep it up.

Thanks bhaski and jamesravid. Your solution is really helpful to me...Thankyou verymuch.

One of my friend gave this solution....

select j1.id, j1.jobno, j1.ownerofjob, ( select min(j3. startdate) from jobs j3 where j3.jobno = j1.jobno) as startdatefrom jobs j1where j1.jobno = 99 and j1.startdate =( select max(startdate) from jobs j2 where j2.jobno = j1.jobno and j2.startdate <= trunc(sysdate))

-------------------
suresh