Update to get check_order

I have a table with values,

col1     col2     col3 
1         0         ABA
1         0         ABB
1         0         ABC
2         0         BBA
2         0         BBB
2         0         BBC

I am trying to update the table to see the number of repetition of col1, in this case col1 has repeated 3 times so each update to col2 incremented by 1.

Requi开发者_JS百科red output after the update table

col1     col2     col3 
1         1         ABA
1         2         ABB
1         3         ABC
2         1         BBA
2         2         BBB
2         3         BBC

A simple row_number() -ing should work

;with TMP as (
select *, row_number() over (partition by col1 order by col3) as RowNum
from tbl
)
update TMP set col2=RowNum

Where

• tbl: is your table name
• partition by col1: resets the row numbering for each col1 group
• order by col3: is the basis for numbering within a col1 group

Assuming you are intending col3 to be in non-descending order, this should do it:

UPDATE MyTable
SET col2=(SELECT COUNT(*) 
          FROM MyTable AS T2 
          WHERE T2.col1=T1.col1 AND T2.col3<=T1.col3)
FROM MyTable AS T1

You will get duplicates in col2, if there are duplicates in col3 for a particular col1 value.

In case you are interested, here is a pretty verbose (and more expensive execution wise) solution using a ranking function. It has the same issue (i.e., the count gets repeated) for duplicates in col1/col3, as the previous solution:

UPDATE MyTable
SET col2=(
     -- In the following query, DISTINCT merges rank dups caused by col3 dups
     -- SELECT TOP(1) MyRank would also work.
     SELECT DISTINCT MyRank 
     FROM (
      SELECT col3,
              DENSE_RANK() OVER (PARTITION BY col1 ORDER BY col3) AS MyRank
      FROM MyTable
      WHERE col1=UpdatedTable.col1
     ) As RankTable
     WHERE RankTable.col3=UpdatedTable.col3)
FROM MyTable AS UpdatedTable