user2783755 - 1 year ago 66
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
``````

``````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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download