Output single row with latest record without null values Oracle

Question

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

Answer 1

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