Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Where clause in partition by column

Datawarehousing_newbieeSep 24 2013 — edited Sep 24 2013

i have in tb1 data like and i need the latest status changed date : when date on which the status of the task changes.

for the data below for taskno :1 i need to get the o/p : 1/2/2013

and for taskno :2  i need the o/p 1/4/2013

tasknoupd_dateold_status new_status
11/1/2013openWIP
11/2/2013WIPClosed
11/3/2013closed closed
21/1/2013openWIP
21/2/2013WIPmore_info_needed
21/3/2013more_info_neededWIP
21/4/2013WIP

closed

im writing the query as below

select taskno, max(upd_date) MAX_UPD_DATE from (

select taskno, upd_date, old_status, new_status, rank from (

select taskno, upd_date, old_status, new_status, rank() over (partition by taskno, old_status, new_status order by upd_date asc) as rank

from tb1

)

where rank = 1

and old_status <> new_status)

group by taskno;


IS there any more efficient way to write this query as the clause "old_status <> new_status" is taking a toll on the performance

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 22 2013
Added on Sep 24 2013
2 comments
841 views