Choose ascending and descending in one field

Question

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

If possible, am I making a choice of sql, for example, at the top of a table with the same table of fields?

 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 comments):

if you do not want to use CTE , then you should 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

Answer 2

use temptables to add line numbers and you can see in the following script

SQL Fiddle