Oracle SQL - Pick last updated values from query for multiple values

Question

I have written the following query that works, however there are tons of duplicates. I would just like to get one set of values for all of the fields. The set of values I'd like to get would be the ones corresponding to the latest date/time from LAST_UPDATED.

SELECT 
nb_policy.LAST_UPDATED
nb_policy.POLICY_ID,
nb_policy.POLICY_NAME,
customer.NAME,
customer.VANTIVE_ID,
customer.SIEBEL_ID,
NBRP.JOB.JOB_ID,
NBRP.JOB.JOB_TYPE,
NBRP.JOB.JOB_STATE,
NBRP.JOB.SCHEDULE_NAME,
NBRP.JOB.SCHEDULE_TYPE,
NBRP.JOB.KBYTES,
NBRP.JOB.BACKUP_TYPE
FROM nb_policy
LEFT JOIN customer 
ON nb_policy.CUSTOMER_ID = customer.CUSTOMER_ID
RIGHT JOIN NBRP.JOB 
ON nb_policy.POLICY_NAME = NBRP.JOB.CLASS_NAME
WHERE customer.ACTIVE = 1 AND (customer.VANTIVE_ID > 0 OR customer.SIEBEL_ID > 0)

Table Properties:

customer.NAME - VARCHAR2
customer.VANTIVE_ID - VARCHAR2
customer.SIEBEL_ID - VARCHAR2
customer.ACTIVE - NUMBER
nb_policy.LAST_UPDATED - CHAR
nb_policy.POLICY_ID - CHAR
nb_policy.POLICY_NAME - VARCHAR2
NBRP.JOB.JOB_ID - NUMBER
NBRP.JOB.JOB_TYPE - NUMBER
NBRP.JOB.JOB_STATE - NUMBER
NBRP.JOB.SCHEDULE_NAME - VARCHAR2
NBRP.JOB.SCHEDULE_TYPE - NUMBER
NBRP.JOB.KBYTES - NUMBER
NBRP.JOB.BACKUP_TYPE - NUMBER

After editing the code per vercelli I am receiving the error: SQL Error [1722] [42000]: ORA-01722: invalid number

Below is vercelli's code:

SELECT * 
FROM   (SELECT nb_policy.last_updated, 
               nb_policy.policy_id, 
               nb_policy.policy_name, 
               customer.name, 
               customer.vantive_id, 
               customer.siebel_id, 
               nbrp.job.job_id, 
               nbrp.job.job_type, 
               nbrp.job.job_state, 
               nbrp.job.schedule_name, 
               nbrp.job.schedule_type, 
               nbrp.job.kbytes, 
               nbrp.job.backup_type, 
               Row_number() 
                 over ( 
                   PARTITION BY nb_policy.last_updated, nb_policy.policy_id, 
                 nb_policy.policy_name, customer.name, customer.vantive_id, 
                 customer.siebel_id, 
                 nbrp.job.job_id, nbrp.job.job_type, nbrp.job.job_state, 
                 nbrp.job.schedule_name, 
                 nbrp.job.schedule_type, nbrp.job.kbytes, nbrp.job.backup_type 
                   ORDER BY nb_policy.last_updated DESC) AS rn 
        FROM   nb_policy 
               left join customer 
                      ON nb_policy.customer_id = customer.customer_id 
               right join nbrp.job 
                       ON nb_policy.policy_name = nbrp.job.class_name 
        WHERE  customer.active = 1 
               AND ( customer.vantive_id > 0 
                      OR customer.siebel_id > 0 )) t 
WHERE  rn = 1

vercelli · Accepted Answer · 2016-06-23 07:41:02Z

2

Try this, changing /*list of fields on group*/ for the set of columns you want to show the last value.

I have written the following query that works, however there are tons of duplicates. I would just like to get one set of values for all of the fields. The set of values I'd like to get would be the ones corresponding to the latest date/time from LAST_UPDATED.

SELECT * 
FROM   (SELECT nb_policy.last_updated, 
               nb_policy.policy_id, 
               nb_policy.policy_name, 
               customer.name, 
               customer.vantive_id, 
               customer.siebel_id, 
               nbrp.job.job_id, 
               nbrp.job.job_type, 
               nbrp.job.job_state, 
               nbrp.job.schedule_name, 
               nbrp.job.schedule_type, 
               nbrp.job.kbytes, 
               nbrp.job.backup_type, 
               Row_number() 
                 over ( 
                   PARTITION BY nb_policy.last_updated, nb_policy.policy_id, 
                 nb_policy.policy_name, customer.name, customer.vantive_id, 
                 customer.siebel_id, 
                 nbrp.job.job_id, nbrp.job.job_type, nbrp.job.job_state, 
                 nbrp.job.schedule_name, 
                 nbrp.job.schedule_type, nbrp.job.kbytes, nbrp.job.backup_type 
                   ORDER BY nb_policy.last_updated DESC) AS rn 
        FROM   nb_policy 
               left join customer 
                      ON nb_policy.customer_id = customer.customer_id 
               right join nbrp.job 
                       ON nb_policy.policy_name = nbrp.job.class_name 
        WHERE  customer.active = 1 
               AND ( customer.vantive_id > 0 
                      OR customer.siebel_id > 0 )) t 
WHERE  rn = 1

edited Jun 23, 2016 at 7:41

answered Jun 22, 2016 at 16:33

vercelli

4,7772 gold badges17 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

JDG Over a year ago

I tried that and received an error code 933. I'd post the code, but this site says it's too long.

vercelli Over a year ago

@JDG Please post the code used as question . There's a syntax error

JDG Over a year ago

This is my first day on this site and they won't allow me to post another question because people have downvoted this post.

vercelli Over a year ago

@JDG Can't see anything wrong in the code. LAST_UPDATED belongs to what table?

vercelli Over a year ago

@JDG I removed as from as t. Try now (sorry I'm on my cell now)

|

Collectives™ on Stack Overflow

Oracle SQL - Pick last updated values from query for multiple values

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related