CODEWITHSUNDEEP
oracle-database
t0mmyK
t0mmyK

Reputation: 1

ORACLE: How to select previous different value?

I have table that stores employee job name, it has the following columns: id; date_from; date_to; emp_id; jobname_id; grade;

Each emp_id can have many consecutive records with the same jobname_id due to many grade changes. How can I select previous different jobname_id omitting those that are the same like the most current one?

Upvotes: 0

Views: 515

Answers (2)

APC
APC

Reputation: 146209

This solution uses the FIRST_VALUE() analytic function to identify each employee's current job. It then filters for all the jobs which dfon't match that one:

select distinct id
       , jobname_id
from ( select id
              , jobname_id
              , first_value(jobname_id) over (partition by id 
                                               order by from_date desc) as current_job
       from employee
       where emp_id = 1234 )
where jobname_id != current_job
order by id, jobname_id 
/

Upvotes: 1

Ollie
Ollie

Reputation: 17538

Will this work for your issue:

SELECT DISTINCT 
       e1.emp_id,
       e1.jobname_id
  FROM employee e1
 WHERE NOT EXISTS
       (SELECT 1
          FROM employee e2
         WHERE e1.emp_id = e2.emp_id
           AND SYSDATE BETWEEN e2.date_from 
                           AND NVL(e2.date_to, SYSDATE + 1));

(This asumes your table is named "employee" and emp_id is the PK value).

It selects unique emp_id, jobname_id values where the emp_id, jobname_id values are not current.

EDIT: I agree with Chin Boon that fundamentally this is a design issue and perhaps that should be addressed rather than working around the problem.

Upvotes: 0

Related Questions