With an SQL Query how do we get the output of 2 columns, the first one being a column sorted in asc order and the second one with the order desc and both are same columns.
ex:
emp table:
empid
1
5
9
4
The query output should be
empid_1 empid_2
1 9
4 5
5 4
9 1
What OP tried so far
WITH emp1
AS (SELECT ROWNUM a,
empno
FROM (SELECT empno
FROM emp
ORDER BY 1 ASC)),
emp2
AS (SELECT ROWNUM b,
empno
FROM (SELECT empno
FROM emp
ORDER BY 1 DESC))
SELECT emp1.empno,
emp2.empno
FROM emp1,
emp2
WHERE emp1.a = emp2.b;
If you use a common table expression/sub-query factoring clause then you only need to access the table once:
with the_data as (
select empid
, row_number() over ( order by empid ) as id_asc
, row_number() over ( order by empid desc ) as id_desc
from emp
)
select a.empid as empid_asc
, d.empid as empid_desc
from the_data a
join the_data d
on a.id_asc = d.id_desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments