Sorting the result based on 2 columns Oracle-CodePudding

I have two columns Date and Number with null values in date. Can I sort with date and number col together checking if date is null then sort with number.

dt             num
3/20/2022       1
3/16/2022       3
3/17/2022       4
3/18/2022       5
NULL            6
NULL            7
3/19/2022       8

*Expected Output*
dt             num
3/16/2022       3
3/17/2022       4
3/18/2022       5
NULL            6
NULL            7
3/19/2022       8
3/20/2022       1

CodePudding user response：

We need to sort by the date if there is one and if there is not we search the previous row where the date is not null.
This does mean that we are running a sub-query per line so it will be slow for large queries.

create table d(
dt date,
num int);

insert into d (dt, num)
select to_date('2022-03-20','YYYY-MM-DD'),1 from dual union all
select to_date('2022-03-16','YYYY-MM-DD'),3 from dual union all
select to_date ('2022-03-17','YYYY-MM-DD'), 4 from dual union all
select  to_date('2022-03-17','YYYY-MM-DD'),5 from dual union all
select  to_date('2022-03-18','YYYY-MM-DD'),6 from dual union all
select  to_date('2022-03-16','YYYY-MM-DD'),10 from dual union all
select  to_date('2022-03-19','YYYY-MM-DD'),9 from dual;

insert into d ( num)
select     7 from dual union all
select     8 from dual ;

select 
  dt,
  num,
  ( select dt 
    from d 
    where num <= d1.num and dt is not null 
    order by num desc 
    fetch next 1 rows only 
  ) as dt_plus
from d d1
order by dt_plus,num;

DT        | NUM | DT_PLUS  
:-------- | --: | :--------
16-MAR-22 |   3 | 16-MAR-22
16-MAR-22 |  10 | 16-MAR-22
17-MAR-22 |   4 | 17-MAR-22
17-MAR-22 |   5 | 17-MAR-22
18-MAR-22 |   6 | 18-MAR-22
null      |   7 | 18-MAR-22
null      |   8 | 18-MAR-22
19-MAR-22 |   9 | 19-MAR-22
20-MAR-22 |   1 | 20-MAR-22

db<>fiddle here