CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Akash Burnwal
Akash Burnwal

Reputation: 25

Output single row with latest record without null values Oracle

I have a table which looks like below.

id job_id object message last_Exception second_last_Exception
312 1 abc deadlock 26-04-2021 null
312 0 abc connection reset null 25-04-2021
313 0 cde connection reset 25-04-2021 null
313 1 cde deadlock null 24-04-2021

Basically i have to print the latest record for each object and if the second_last_Exception_time is null then it should fetch it from the next record. It is also given that for a single object there will be only two rows.

Ideally the output should be like this.

id job_id object message last_Exception second_last_Exception
312 1 abc deadlock 26-04-2021 25-04-2021
313 0 cde connection reset 25-04-2021 24-04-2021

Upvotes: 0

Views: 50

Answers (1)

ekochergin
ekochergin

Reputation: 4129

the only idea I have is to self join your table

select t1.id, t1.job_id, t1.object, t1.message, t1.last_exception, t2.second_last_exception
  from some_table t1
  join some_table t2
    on t1.id = t2.id and t1.object = t2.object
 where t1.last_exception is not null
   and t2.second_last_exception is not null

UPD. if second_last_exception is ALWAYS less than last_exception (which it seems logical to me), you may use lead function and filter lines you don't need later in the outer query

select * 
  from (select id, job_id, message, last_exception, 
        lead(second_last_exception) over(partition by id, object order by nvl(last_exception, second_last_exception) desc) sec_last_exc
          from some_table)
 where last_exception is not null

Upvotes: 1

Related Questions