I have the following columns:
a | null
x | f
null | a
i | n
I need both columns sorted alphabetically with nulls at the bottom, like so:
a | a
i | f
x | n
null | null
Is there anyway to do this in mysql?
Each column must be sorted independently from the other and then recombine the rows by the position of each value in that order.
With ROW_NUMBER() window function:
select t1.col1, t2.col2
from (
select col1, row_number() over(order by col1 is null, col1) rn
from tablename
) t1 inner join (
select col2, row_number() over(order by col2 is null, col2) rn
from tablename
) t2 on t2.rn = t1.rn
See the demo.
Or with CTEs:
with
cte1 as (select col1, row_number() over(order by col1 is null, col1) rn from tablename),
cte2 as (select col2, row_number() over(order by col2 is null, col2) rn from tablename)
select cte1.col1, cte2.col2
from cte1 inner join cte2
on cte2.rn = cte1.rn
See the demo.
Results:
| col1 | col2 |
| ---- | ---- |
| a | a |
| i | f |
| x | n |
| null | null |