How can I write MySQL query that filters even and odd number from a a single column and return the results in 2 columns as below

On MySQL 8+, we can use ROW_NUMBER() here along with the modulus:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Numb % 2 ORDER BY Numb) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN Numb % 2 = 0 THEN Numb END) AS even,
    MAX(CASE WHEN Numb % 2 = 1 THEN Numb END) AS odd
FROM cte
GROUP BY rn
ORDER BY 1;

For MySQL versions prior to 8.0, we can emulate row number using user variables. Supposing the numbers always come in pairs,an inner join is suffice. However, what if the numbers are not in pairs. e.g from 1 to 13, or from 1 to 12 plus 14 . Then an OUTER join should be used to make sure no number is left out. In addition, as we do not know if an odd number or an even number is redundant, we need to use a FULL JOIN which in MySQL's case can be simulated using a UNION between a LEFT JOIN and a RIGHT JOIN. Here is the code written and tested in workbench.

select odd,even
from
    (select t1.numb as odd, t2.numb as even 
     from   (select numb,@row_id1:=@row_id1+1 as row_id1
            from test,(select @row_id1:=0) t
            where numb % 2 = 1
            order by numb) t1
            left join
            (select numb,@row_num1:=@row_num1+1 as row_num1
            from test,(select @row_num1:=0) t
            where numb % 2 = 0
            order by numb) t2
     on row_id1=row_num1
    UNION
    select t1.numb as odd, t2.numb as even 
     from   (select numb,@row_id2:=@row_id2+1 as row_id2
            from test,(select @row_id2:=0) t
            where numb % 2 = 1
            order by numb) t1
            right join
            (select numb,@row_num2:=@row_num2+1 as row_num2
            from test,(select @row_num2:=0) t
            where numb % 2 = 0
            order by numb) t2
            on row_id2=row_num2) union_t
order by -odd desc;

Two things to note: First of all,as an ORDER BY clause does not work in UNION, we can make the UNION a derived table so the outer layer SELECT statement can effectively apply the ORDER BY clause. Secondly, by default, null is treated as the lowest in ascending order. To make null last in ASC, we can add a minus before the column name and use DESC . Below are some test result:

-- result for numbers 1 to 12:
# odd, even
1, 2
3, 4
5, 6
7, 8
9, 10
11, 12

-- result for numbers 1 to 13 (note: workbench displays null as whitespace):
# odd, even
1, 2
3, 4
5, 6
7, 8
9, 10
11, 12
13, 

-- result for numbers 1 to 14 plus 16 18 (note: workbench displays null as whitespace):
# odd, even
1, 2
3, 4
5, 6
7, 8
9, 10
11, 12
13, 14
, 16
, 18

before mysql 8:

with v_value as (select even,@rownum:=@rownum+1 as rownum from (select @rownum:=0) a,test b order by even)
select even,(select even from v_value where rownum=c.rownum-1)
  from v_value c
 where mod(even,2)=0;

mysql 8:

select * from (select even,lag(even) over(order by even asc) as odd from test order by even) a where mod(even,2)=0;