How to select the same column twice with a different order

Question

Here is my problem. I need to order the same speakers in different ways in the same choice. Go first and then go down.

Example:

Table1

+--------+-----+
| Name   | Age |
+--------+-----+
| Coa    | 20  |
| Bami   | 12  |
| Alice  | 50  |
+--------+-----+

The results should be:

+------+-----+
| Age  | Age |
+------+-----+
| 12   | 50  |
| 20   | 20  |
| 50   | 12  |
+------+-----+

I want the same column to order Ascendant first and then Descendant.

I'm trying to

Query

Select t1.age, t2.age
from table1 t1 
inner join table1 t2 
on t1.name=t2.name
order by t1.age asc, t2.age desc

But as a result, both columns are ordered in the same way.

Does anyone know how to solve this problem?

Answer 1

This is a really strange requirement. You can do this using row_number()and connections:

select t1a.age, t1b.age
from (select t1.*, row_number() over (order by age asc) as seqnum
      from table1 t1
     ) t1a join
     (select t1.*, row_number() over (order by age desc) as seqnum
      from t1
     ) t1b
     on t1a.seqnum = t1b.seqnum
order by t1a.age;

Answer 2

SQL Fiddle Demo

WITH CTE AS
(
    SELECT NAME,
            AGE,
            ROW_NUMBER() OVER(ORDER BY AGE ASC) AS AC,
            ROW_NUMBER() OVER(ORDER BY AGE DESC) AS DC
    FROM YOURTABLE
)

SELECT C1.AGE,C2.AGE
FROM CTE C1 
        INNER JOIN CTE C2 ON C1.AC=C2.DC