Sql query to sort data on one column only but not change the others columns

I want a 开发者_Python百科SQL query to display the following data

ID Name
1  AAA
2  BBB
3  CCC
4  DDD

as this:

ID Name
4  AAA
3  BBB
2  CCC
1  DDD

without changing the other columns.

Kindly suggest?

Thanks

You could use row_number to number the table in two directions, and zip those together:

declare @t table (id int, name varchar(4))
insert @t values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')

; with  numbered as
        (
        select  row_number() over (order by id) as rn1
        ,       row_number() over (order by id desc) as rn2
        ,       *
        from    @t
        )
select  t2.id
,       t1.name
from    numbered t1
join    numbered t2
on      t1.rn1 = t2.rn2

This prints:

id  name
4   AAA
3   BBB
2   CCC
1   DDD

I'm going with something like this :

SELECT t2.ID, t1.NAME
FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS rownumber,
           Name
    FROM MyTable) as t1
    INNER JOIN
        (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rownumber,
                ID
         FROM MyTable) as t2
ON t1.rownumber = t2.rownumber

You have to set to each row a number for the Name field, and for the ID field, in different order, and then join between them to retrieve the datas in different order.

I would use a subselect with order by clause for the ID column.