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