T-SQL Re-order rows in table

sql, sql-server-2008, t-sql

Solution

Try this:

SELECT VersionID, DocumentID, ROW_NUMBER() OVER(PARTITION BY DocumentID ORDER BY VersionID DESC) as VersionNo 
FROM YOUR_TABLE

If you need to update your table, use this:

;WITH CTE AS (
    SELECT VersionID, DocumentID, ROW_NUMBER() OVER(PARTITION BY DocumentID ORDER BY VersionID DESC) as VersionNoNew
    FROM YOUR_TABLE
)
UPDATE CTE
SET VersionNo = VersionNoNew

Problem

Apolgoies in advance if this is a repeated question, which I failed to look up :( I have a table that looks like: ``` VersionID DocumentID VersionNo 111 12345 1 112 12345 2 113 12345 3 ``` I need to reverse the order of the 'VersionNo' column (all other columns remains unchanged) to as follow: ``` VersionID DocumentID VersionNo 111 12345 3 112 12345 2 113 12345 1 ``` I was thinking along the lines of a CTE and ROW_NUMBER() OVER but not been able to get it to work correctly... Any assistance would be highly appreciated. Thanks

Original source