MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (col1 int,  col2 varchar(10));
insert into test values(1     , 'a');
insert into test values(2     , 'b');
insert into test values(3     , 'c');
insert into test values(4     , 'd');
select t1.col1, t2.col2
from test t1 join
     test t2
     on t1.col1 + t2.col1 =(select max(col1)+1 from test)
order by t1.col1     


col1
col2


1
d

2
c

3
b

4
a


select t1.col1,t2.col2 from
(select col1,col2,row_number()over(order by col1)rn from test)t1
inner join (select col1,col2,row_number()over(order by col1 desc)rn from test)t2
on t1.rn=t2.rn


col1
col2


1
d

2
c

3
b

4
a