Help with sql server query

Question

Hi

I have this table:

proj - nun - X
aaa  -  1   - 0
aaa  -  2  -  0
bbb  -  3 -   0
bbb  -  4 -   0
bbb  -  5 -   0
bbb  -  6 -   0
ccc  -  7  -  0

i need to get this

proj - nun - X
aaa  -  1   - 1
aaa  -  2  -  2
bbb  -  3 -   1
bbb  -  4 -   2
bbb  -  5 -   3
bbb  -  6 -   4
ccc  -  7  -  1

how I can do it with sql query ?

(sql server 2008)

Answer 1

I think this will do it, using ROW_NUMBER:

SELECT 
  proj,
  nun,
  ROW_NUMBER() OVER (PARTITION BY proj ORDER BY nun) X
FROM table

Answer 2

select proj, nun, ROW_NUMBER() over (partition by proj order by proj, nun) as x from table1

Answer 3

Assuming your table name is MyTable,

UPDATE T
SET T.x = N.x
FROM MyTable T
JOIN (
  SELECT proj, nun, RANK() OVER (PARTITION BY proj ORDER BY nun) as x
  FROM MyTable
) N ON T.proj = N.proj and T.nun = N.nun

Will update the column x in your table with the right rank. The simple way to remember this is that you are Partitioning your rows by proj, ordering them by nun, and then apply the RANK() (or ROW_NUMBER()) function.