title: Assigning a Row Number in SQL Server, but grouped on a value
body: <p>I want to select 2 columns from a table, and assign a int value to each value.  However, I want the 1st column ID to be the same for all values that are the same. </p>
  
  <p>For the 2nd column, I want each value to numbered as well, but partitioned by the first column.  I have figured this piece out, but I can't get the first part to work.  </p>
  
  <p>Here is the test scenario I'm using.</p>
  
  <pre><code>DECLARE @TestTable as Table (Column1 char(1), Column2 char(1))
  
  INSERT INTO @TestTable SELECT 'A','A' 
  INSERT INTO @TestTable SELECT 'A','B' 
  INSERT INTO @TestTable SELECT 'A','C' 
  INSERT INTO @TestTable SELECT 'B','D' 
  INSERT INTO @TestTable SELECT 'B','E' 
  INSERT INTO @TestTable SELECT 'B','F' 
  INSERT INTO @TestTable SELECT 'B','G' 
  INSERT INTO @TestTable SELECT 'B','H' 
  INSERT INTO @TestTable SELECT 'C','A' 
  INSERT INTO @TestTable SELECT 'C','B' 
  INSERT INTO @TestTable SELECT 'C','C' 
  
  
  SELECT 
      Row_Number() OVER (Partition BY Column1 ORDER BY Column1) as Column1_ID,
      Column1,
      Row_Number() OVER (Partition BY Column1 ORDER BY Column1, Column2) as Column2_ID,
      Column2
  FROM @TestTable
  </code></pre>
  
  <p>When I run this, the values in Column2_ID are correct, but I would like the values for Column1_ID to be as follows.</p>
  
  <pre><code>Column1_ID  Column1  Column2_ID  Column2
  1             A        1           A
  1             A        2           B
  1             A        3           C
  2             B        1           D
  2             B        2           E
  2             B        3           F
  2             B        4           G
  2             B        5           H
  3             C        1           A
  3             C        2           B
  3             C        3           C
  </code></pre>