CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
vborutenko
vborutenko

Reputation: 4443

Calculate column in SQL Server database

I have table like this

 id buildingId order  title
 --------------------------
  1      2      null  test1
  2      2      null  test2
  3      2      null  test3
  4      3      null  test4
  5      3      null  test5
  6      5      null  test6

I need calculate order value for every row. I need after execute sql query get this table

 id buildingId order  title
  1      2      0     test1
  2      2      1     test2
  3      2      2     test3
  4      3      0     test4
  5      3      1     test5
  6      5      0     test6

How can I do that?

Upvotes: 1

Views: 76

Answers (1)

John Woo
John Woo

Reputation: 263733

WITH recordList
AS
(
    SELECT  ID, buildingID, [order], title,
            ROW_NUMBER() OVER (PARTITION BY buildingID
                                ORDER BY ID) rn
    FROM    tableName
)
UPDATE recordList
SET [order] = rn - 1

Upvotes: 1

Related Questions