0

Let's say I have table like this:

Column1            Column2
C                     2
B                     1
A                     3

I need to exchange values in the second column to get this:

Column1            Column2
C                     3
B                     2
A                     1

The goal is only for numeric column to have values sorted to follow alphabetical order on another column. The actual table has multiple columns and column 1 is people's name, while column 2 two is rank for rendering column 1 values in UI.

What is the most optimal way to do this? I am doing this from C# code, on SQL server and have to use System.Data.SqlClient.SqlCommand because of transaction. But maybe it's not important if this can all be done from SQL.

Thank you!

Срба
  • 463
  • 3
  • 17

1 Answers1

3

So you need to update Column2 with the row-number according toColumn1?

You can use ROW_NUMBER and a CTE:

WITH CTE AS 
(
    SELECT Column1, Column2, RN = ROW_NUMBER() OVER (ORDER BY Column1)
    FROM MyTable
)
UPDATE CTE SET Column2 = RN;

This updates the table MyTable and works because the CTE selects a single table. If it contains more than one table you have to JOIN the UPDATE with the CTE.

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • My question was not above row numbers, I have edited it to add more details. It about sort numeric column values to follow alphabetic ordering of another nvarchar column. But I think in my case will be able to do something even with this. – Срба Oct 09 '15 at 11:04