-
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 .
-
Re: regarding sql query
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)
-
Re: regarding sql query
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
-
Re: regarding sql query
bhaski please post only the code part using proper code tags.
Kindly do not post the execution plan.
-
Re: regarding sql query
[QUOTE=jamesravid;14875]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[/QUOTE]
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
-
Re: regarding sql query
sorry
next time i will do it properly.
sorry again
-
Re: regarding sql query
any way you are doing great work .
Youa re an asset to the oracle forum.
keep it up.
-
Re: regarding sql query
Thanks bhaski and jamesravid. Your solution is really helpful to me...Thankyou verymuch.
-
Re: regarding sql query
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