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!

Filtering maximum rank using rank function ?

deekayAug 16 2010 — edited Aug 16 2010
Hi ,

I have two table emp_changes(empno(number),operation_time(timestamp),operation(varchar))
and another table emp_metadata(tablename(varchar),max_time(timestamp))(it has got one row )


using this query :
    select empno,operation,RANK() OVER (PARTITION BY empno ORDER BY operation_time) "time" from emp_changes
where empno in (select empno from emp_changes where operation_time BETWEEN (select max_time from emp_metadata) AND sysdate)
I am getting this output:


EMPNO OPERATION time
---------------------- -------------------------------------------------- ----------------------
8009 INSERT 1
8009 UPDATE 2
8009 DELETE 3
8010 INSERT 1
8013 INSERT 1
9001 UPDATE 1
9001 DELETE 2



can anyone please tell me how to achive this like ,I want to view only distinct empno,operation with max rank in time like:

EMPNO OPERATION
8009 DELETE
9001 DELETE


Thanks ,
Deekay
This post has been answered by 737905 on Aug 16 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 13 2010
Added on Aug 16 2010
3 comments
5,823 views