Order by statement - ordering by multiple columns

Question

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 1

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 

Answer 2

Do it like this:

select case when C in not null then C else A end as A,B,C from table

It same as like this:

Declare @c nchar(50)
Declare @a nchar(50)
set @c = 'record' 
set @a = 'sample'
select case  when  @c is not null then @c else @a end as A,@c as C