Force null values to be considered as earlier dates using the row_number function

Question

I have a table that lists documents associated with a patient. Some documents will have a RECEIVED_TIME, while other will not. I need to have the most-recent RECEIVED_TIME if it exists.

I'm using the row_number function to create a sequence number (SEQ) based on the RECEIVED_TIME; I would restrict the records to SEQ=1.

Unfortunately, null values are being considered as 'later' than the RECEIVED_TIME, when I need them to be considered as 'earlier'.

The SQL:

with

data as
(
  select  v.*
          -- sort the most-recent value first
          ,row_number() over (partition by pat_id order by RECEIVED_TIME desc) SEQ
  from (

         select 'A' PAT_ID, 100 ID, 'Obtain' STATUS, '2019-04-19 00:00:00' RECEIVED_TIME from dual
         union all
         select 'A' PAT_ID, 101 ID, 'Unable to Obtained' STATUS, null RECEIVED_TIME from dual

          union all
         select 'B' PAT_ID, 102 ID, 'Obtained' STATUS, '2019-04-18 00:00:00' RECEIVED_TIME from dual
          union all
         select 'B' PAT_ID, 103 ID, 'Obtained' STATUS, '2019-04-19 00:00:00' RECEIVED_TIME from dual

         union all

         select 'C' PAT_ID, 104 ID, 'Unable to Obtained' STATUS, null RECEIVED_TIME from dual

       ) v
)

select  *
from  data

Results:

PAT_ID,ID,STATUS,RECEIVED_TIME,SEQ
A,101,Unable to Obtained,<NULL>,1  <-- null is 'later'
A,100,Obtain,2019-04-19 00:00:00,2
B,103,Obtained,2019-04-19 00:00:00,1
B,102,Obtained,2019-04-18 00:00:00,2
C,104,Unable to Obtained,<NULL>,1

Desired results:

PAT_ID,ID,STATUS,RECEIVED_TIME,SEQ
A,100,Obtain,2019-04-19 00:00:00,1
A,101,Unable to Obtained,<NULL>,2  <-- null should be 'earlier'
B,103,Obtained,2019-04-19 00:00:00,1
B,102,Obtained,2019-04-18 00:00:00,2
C,104,Unable to Obtained,<NULL>,1

Is there a way to achieve this?

Answer 1

It's pretty simple: add NULLS LAST to the ORDER BY clause in the window function:

with data as
(
  select  v.*
          -- sort the most-recent value first
          ,row_number() over (partition by pat_id 
                               order by RECEIVED_TIME desc nulls last) SEQ
  from (    
     select 'A' PAT_ID, 100 ID, 'Obtain' STATUS, '2019-04-19 00:00:00' RECEIVED_TIME from dual union all
     select 'A' PAT_ID, 101 ID, 'Unable to Obtained' STATUS, null RECEIVED_TIME from dual union all
     select 'B' PAT_ID, 102 ID, 'Obtained' STATUS, '2019-04-18 00:00:00' RECEIVED_TIME from dual union all
     select 'B' PAT_ID, 103 ID, 'Obtained' STATUS, '2019-04-19 00:00:00' RECEIVED_TIME from dual union all    
     select 'C' PAT_ID, 104 ID, 'Unable to Obtained' STATUS, null RECEIVED_TIME from dual
           ) v
)    
select  *
from  data

Here's a db<>fiddle