Select ascending and descending on the same field

Question

___________________
| field1 | field1 |
|________|________|
|    A   |   E    |
|    B   |   D    |
|    C   |   C    |
|    D   |   B    |
|    E   |   A    |
|________|________|

If possible I do the sql selection like upside table with same field same table?

select t1.c, t2.c from
(
    WITH cte AS
    (
        SELECT 
            c,
            ROW_NUMBER() OVER(ORDER BY c DESC) AS n,
            ROW_NUMBER() OVER(ORDER BY c ASC) AS m
        FROM @t
    )
    SELECT t1.c, t2.c
    FROM cte t1
    JOIN cte t2 ON t2.n = t1.m
)

can i do something like this?

Answer 1

DECLARE @t TABLE ( c CHAR(1) )

INSERT INTO @t
VALUES ('a'),('b'),('c'),('d'),('e')

;WITH cte AS
(
    SELECT 
        c,
        ROW_NUMBER() OVER(ORDER BY c DESC) AS n,
        ROW_NUMBER() OVER(ORDER BY c ASC) AS m
    FROM @t
)

SELECT t1.c, t2.c
FROM cte t1
JOIN cte t2 ON t2.n = t1.m

Edit (for comment):

if you don't want to use a CTE then you would write it as

SELECT t1.c, t2.c 
FROM ( 
    SELECT c
        , ROW_NUMBER() OVER(ORDER BY c ASC) AS m 
    FROM @t 
) t1
JOIN ( 
    SELECT c
        , ROW_NUMBER() OVER(ORDER BY c DESC) AS n
    FROM @t 
) t2 ON t2.n = t1.m

SQLFiddle Demo