add row number based on given column value SQL

Question

I would like to add numbers to my rows based on the product value:

Lets say Ive got table:

ProductId Value
001 10
002 30
003 20

then when I order it by Value asc I would get:

ProductId Value
001 10
003 20
004 30

Now I want to add new column called Position and the result would be like this:

ProductId Value Position
001 10 1
002 30 3
003 20 2

Thanks for any help how can I do this.

My database serrver is sql server 2008R2

Your example still isnt congruent. You've now added a ProductId of 004 that doesnt fit in the previous or subsequent values. — crthompson
– crthompson, Commented Sep 7, 2014 at 17:21

Martin Smith · Accepted Answer · 2014-09-07 17:27:40Z

3

You can use row_number

WITH T 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(ORDER BY ProductId) AS P 
         FROM   tablename) 
UPDATE T 
SET    Position = P

Of course keeping this in synch following changes in the data may be hassle than it's worth if this is not a one off requirement.

answered Sep 7, 2014 at 17:27

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

crthompson · Accepted Answer · 2014-09-07 17:29:03Z

0

Ok so I figured out the answer: First add column: 'Position' and then:

Update A
SET A.Position = B.Position
from [Product] A join (
SELECT [ProductId]
      ,[Value]
      ,ROW_NUMBER() over (order by Value) as Position
  FROM Product)  B
  ON A.ProductId= B.ProductId

edited Sep 7, 2014 at 17:29

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

answered Sep 7, 2014 at 17:26

gruber

29.9k35 gold badges132 silver badges221 bronze badges

1 Comment

Martin Smith Over a year ago

You don't need the join.

Collectives™ on Stack Overflow

add row number based on given column value SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related