user2783755 user2783755 - 3 months ago 9
SQL Question

Order by statement - ordering by multiple columns

I have table like this:

A B C
-----------------------
111 3
777
333 1
555 2
333
777 4
888 5


So, i have order by statement “order by B” and the I have result like this:

A B C
----------------------
333 1
555 2
111 3
777 4
888 5
777
333


However, what can I do to get this sorting:

A B C
-----------------------
333 1
333
555 2
111 3
777 4
777
888 5


In case when column C not null, I should put this row after row where A = C

Thanks!

So, in case of this:

with a(a,b,c) as (select 111,4, null from dual union all
select null,null,777 from dual union all
select 333,1,null from dual union all
select 555,2, null from dual union all
select null,null, 333 from dual union all
select 777, 4, null from dual union all

select 444,null, 333 from dual union all

select 888, 5, null from dual union all
select null,null,777 from dual )

select a.*
from a
order by last_value(b ignore nulls)
over (partition by CASE when b is null then c else a end order by b), b nulls last


I have that output (C 777 are after A 111, because of B values are the same = 4):

A B C
--------------------
333 1
444 333
333
555 2
777 4
111 4
777
777
888 5


But I want to get this:

A B C
--------------------
333 1
444 333
333
555 2
777 4
777
777
111 4
888 5

Answer

may be this help you :

with a(a,b,c) as (select 111,3, null from dual union all
                  select null,null,777 from dual union all
                  select 333,1,null  from dual union all
                  select 555,2, null from dual union all
                  select null,null, 333 from dual union all
                  select 777, 4, null from dual union all
                  select 888, 5, null from dual )

select a.* 
  from a
 order by last_value(b ignore nulls) over (partition by nvl(a,c) order by b), b nulls last

output

333 1   
        333
555 2   
111 3   
777 4   
        777
888 5   

7 rows selected

or as you say later, you can have both not null A and C columns, you can do like this:

with a(a,b,c) as (select 111,3, null from dual union all
                  select null,null,777 from dual union all
                  select 333,1,null  from dual union all
                  select 555,2, null from dual union all
                  select null,null, 333 from dual union all
                  select 777, 4, null from dual union all

                  select 444,null, 333 from dual union all

                  select 888, 5, null from dual )

select a.*
  from a
 order by last_value(b ignore nulls) 
       over (partition by CASE when b is null then c else a end order by b), b nulls last

output

         A          B          C
       333          1            
                             333 
       444                   333 
       555          2            
       111          3            
       777          4            
                             777 
       888          5            

 8 rows selected