Reputation: 45
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 without any duplicate values and both are same columns.
EMP table:
EMP_NAME
Megha
Vicky
Neha
Sachin
Neha
The query output should be
EMP_NAME_ASC EMP_NAME_DESC
Megha Vicky
Neha Sachin
Sachin Neha
Vicky Megha
I tried below solution How to sort a same column both in asc order and desc order
but that is not giving me distinct values. Any help would be appreciated. Thank you in advance.
Upvotes: 2
Views: 318
Reputation: 93191
With a little modification on Gordon Linoff's answer, you can get distinct names:
;WITH
cte AS
(
SELECT EMP_NAME
, DENSE_RANK() OVER (ORDER BY EMP_NAME ASC) AS RankAsc
, DENSE_RANK() OVER (ORDER BY EMP_NAME DESC) AS RankDesc
, ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME)
AS RowNumber
FROM #EMP
)
SELECT cte1.EMP_NAME AS EMP_NAME_ASC
, cte2.EMP_NAME AS EMP_NAME_DESC
FROM cte cte1
INNER JOIN cte cte2 ON cte1.RankAsc = cte2.RankDesc
WHERE cte1.RowNumber = 1
AND cte2.RowNumber = 1
The ROW_NUMBER
gives a sequential number each time the same name appears. The first time Neha
appears it will get 1
, the second time it will get 2
and so on. We are interested in the first time each name appears and hence get the distinct names only.
Upvotes: 1
Reputation: 1271241
You are two separate lists in columns. This is not a SQL-ish way to store data -- the rows don't mean anything. But you can do this, using row_number()
:
select a.name, d.name
from (select name, row_number() over (order by name asc) as seqnum
from t
) a join
(select name, row_number() over (order by name desc) as seqnum
from t
) d
on a.seqnum = d.seqnum;
Upvotes: 1