SQL reverse function

Question

Is there any function that reverse the whole column?

Sample like this:

Result:

Answer 1

Hi @Lylyy

As far as I know, there is not such function.

If you want to access data from a previous row or next row, you might need LAG() or LEAD() function.

Best regards,

Cosmog Hong

Answer 2

Hi @Lylyy,

Please try the following.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [value] INT);
INSERT @tbl ([value]) VALUES
(111), (222), (333);
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

-- after
SELECT ROW_NUMBER() OVER (ORDER BY [value] DESC) AS ID
	, [value]
FROM @tbl;

Answer 3

pretty simple set operation:

update test 
    set value = t2.value
from (
    select id, ROW_NUMBER() over (order by id) as rn
    from test
) t1
join (
    select id, value, ROW_NUMBER() over (order by id desc) as rn
    from test
) t2 on t1.rn = t2.rn