TSQL increment a colum and start over based on another column

Question

I have a table as follows:

colA   colB(unique)   colC
---------------------------------
1      1449           0.50000000
1      1451           1.03400000
1      2404           5.98750000
1      1454           6.00000000
3      1465           1.40000000
3      1467           1.56000000
3      1476           3.00000000
3      1469           4.00000000
3      1490           5.00000000

I want to make col C an incremeting whole number (ok to leave scale) that starts over when colA changes, like so:

colA  colB (unique)  colC
----------------------------------
1     1449           1.00000000
1     1451           2.00000000
1     2404           3.00000000
1     1454           4.00000000
3     1465           1.00000000
3     1467           2.00000000
3     1476           3.00000000
3     1469           4.00000000
3     1490           5.00000000

Any ideas for doing this? I'm on SQL Server 2008. This table could get very large. Thank you!

Answer 1

I wouldn't store ranked values--they need to be maintained. I'd usually use a view for things like this:

CREATE VIEW your_view AS
  SELECT t.cola,
         t.colb,
         ROW_NUMBER() OVER(PARTITION BY t.cola
                               ORDER BY t.colc) AS colc
    FROM YOUR_TABLE t

But you can use a WITH statement in an UPDATE:

WITH summary AS (
  SELECT t.cola,
         t.colb,
         t.colc,
         ROW_NUMBER() OVER(PARTITION BY t.cola
                               ORDER BY t.colc) AS cold
    FROM YOUR_TABLE t)
UPDATE summary
   SET colc = cold