title: How do to the opposite of "row_number() over (partition by [Col] order by [Col])"
body: <p>I am trying to combine duplicate entries in a data table and give them a new number.</p>
  <p>Here is a example data set (<a href="https://data.stackexchange.com/stackoverflow/query/81485/query-for-http-stackoverflow-com-questions-12695858?opt.textResults=true" rel="nofollow noreferrer">runnable copy</a>)</p>
  <pre><code>declare @tmpTable table
      (ID Varchar(1), 
       First varchar(4), 
       Last varchar(5), 
       Phone varchar(13),
       NonKeyField varchar(4))
  
  insert into @tmpTable select 'A', 'John', 'Smith', '(555)555-1234', 'ASDF'
  insert into @tmpTable select 'B', 'John', 'Smith', '(555)555-1234', 'GHJK'
  insert into @tmpTable select 'C', 'Jane', 'Smith', '(555)555-1234', 'QWER'
  insert into @tmpTable select 'D', 'John', 'Smith', '(555)555-1234', 'RTYU'
  insert into @tmpTable select 'E', 'Bill', 'Blake', '(555)555-0000', 'BVNM'
  insert into @tmpTable select 'F', 'Bill', 'Blake', '(555)555-0000', '%^&amp;*'
  insert into @tmpTable select 'G', 'John', 'Smith', '(555)555-1234', '!#RF'
  
  select row_number() over (partition by First, Last, Phone order by ID) NewIDNum, *  
  from @tmpTable order by ID
  </code></pre>
  <p>Right now it gives me the results</p>
  <pre><code>NewIDNum             ID   First Last  Phone         NonKeyField
  -------------------- ---- ----- ----- ------------- -----------
  1                    A    John  Smith (555)555-1234 ASDF
  2                    B    John  Smith (555)555-1234 GHJK
  1                    C    Jane  Smith (555)555-1234 QWER
  3                    D    John  Smith (555)555-1234 RTYU
  1                    E    Bill  Blake (555)555-0000 BVNM
  2                    F    Bill  Blake (555)555-0000 %^&amp;*
  4                    G    John  Smith (555)555-1234 !#RF
  </code></pre>
  <p>However that is the opposite of what I want, the <code>NewIDNum</code> resets its counter ever time it finds a new combination of the key. I want all of the same combination to have the same ID. So if it was behaving the way I wanted I would get the following results</p>
  <pre><code>NewIDNum             ID   First Last  Phone         NonKeyField
  -------------------- ---- ----- ----- ------------- -----------
  1                    A    John  Smith (555)555-1234 ASDF
  1                    B    John  Smith (555)555-1234 GHJK
  2                    C    Jane  Smith (555)555-1234 QWER
  1                    D    John  Smith (555)555-1234 RTYU
  3                    E    Bill  Blake (555)555-0000 BVNM
  3                    F    Bill  Blake (555)555-0000 %^&amp;*
  1                    G    John  Smith (555)555-1234 !#RF
  </code></pre>
  <p>What is the correct way to get the results I want?</p>
  <hr />
  <p><strong>I did not include this requirement in the original post</strong>:  I need the <code>NewIDNum</code> to produce the same numbers on subsequent runs of this query for the existing rows if more rows get added (assuming all new rows will have a higher ID &quot;value&quot; if a order by is done on the ID column)</p>
  <p>So if at a latter date the following was done</p>
  <pre><code>insert into @tmpTable select 'H', 'John', 'Smith', '(555)555-1234', '4321'
  insert into @tmpTable select 'I', 'Jake', 'Jons', '(555)555-1234', '1234'
  insert into @tmpTable select 'J', 'John', 'Smith', '(555)555-1234', '2345'
  </code></pre>
  <p>running the correct query again would give</p>
  <pre><code>NewIDNum             ID   First Last  Phone         NonKeyField
  -------------------- ---- ----- ----- ------------- -----------
  1                    A    John  Smith (555)555-1234 ASDF
  1                    B    John  Smith (555)555-1234 GHJK
  2                    C    Jane  Smith (555)555-1234 QWER
  1                    D    John  Smith (555)555-1234 RTYU
  3                    E    Bill  Blake (555)555-0000 BVNM
  3                    F    Bill  Blake (555)555-0000 %^&amp;*
  1                    G    John  Smith (555)555-1234 !#RF
  1                    H    John  Smith (555)555-1234 4321
  4                    I    Jake  Jons  (555)555-1234 1234
  1                    J    John  Smith (555)555-1234 2345
  </code></pre>