CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vishakha Badhan
Vishakha Badhan

Reputation: 45

How to sort a same column both in ASC & DESC order without duplicates

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

Answers (2)

Code Different
Code Different

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

Gordon Linoff
Gordon Linoff

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

Related Questions