title: SQL Server: How to use dense_rank on one column, based on order by another column
body: <p>I have got a table in SQL Server 2008 where I need alternating values for one column, say column <code>alt</code>. Duplicates in that column always need the same value, hence I was thinking about using the <code>dense_rank</code> function for this column <code>alt</code> via % 2.</p>
  
  <p>But there are also zip codes in that table, that I need to order the data by before assigning the alternating values.</p>
  
  <p>So basically after the alternating values based on column <code>alt</code> have been assigned when the data is then ordered by zip code the alternating values really need to be alternating (apart from the duplicates in the 'alt' table of course).</p>
  
  <p>Currently I get a result where the <code>alt</code> values do get alternating values, but when ordering by zip codes, I have sequences of e.g. 0,0,0 via the dense_rank function that are the problem.</p>
  
  <p>I tried using a temp table, but didn't get the expected result with a</p>
  
  <pre><code>select * into #txy ordered by zip 
  </code></pre>
  
  <p>and then doing the desk_rank on that table because the order of a temp table isn't guaranteed.</p>
  
  <p>Any ideas are greatly appreciated!</p>
  
  <p>Cheers,
  Stevo</p>
  
  <p>Edit:</p>
  
  <p>Sample Code:</p>
  
  <pre><code>CREATE TABLE [xy.TestTable](
  [BaseForAlternatingValue] [char](10),
  [zip] [varchar](5)
  ) ON [PRIMARY]
  GO
  
  
  INSERT INTO [xy.TestTable]
         ([BaseForAlternatingValue]
         ,[zip])
   VALUES
         ('cccccccccc','99999'),
         ('bbbbbbbbbb','22222'),
         ('aaaaaaaaaa','12345'),
         ('dddddddddd','33333'),
         ('aaaaaaaaaa','12345'),
         ('bbbbbbbbbb','22222')
  GO
  
  select (DENSE_RANK() OVER (ORDER BY BaseForAlternatingValue)) % 2 as AlternatingValue
      , BaseForAlternatingValue
      , zip
      from [xy.TestTable]
      order by zip
  
  
  Result:
  AlternatingValue    BaseForAlternatingValue zip
  1                      aaaaaaaaaa            12345
  1                      aaaaaaaaaa            12345
  0                      bbbbbbbbbb            22222
  0                      bbbbbbbbbb            22222
  0                      dddddddddd            33333
  1                      cccccccccc            99999
  </code></pre>
  
  <p>The Problem now is that when ordered by zip code the following columns both contain the same value (0) as alternating value. When ordered by zip code the result should really have alternating values, but these alternating values should be based on the column BaseForAlternatingValue.</p>
  
  <pre><code>0                      bbbbbbbbbb            22222
  0                      dddddddddd            33333
  </code></pre>
  
  <p>The expected outcome should be:</p>
  
  <pre><code>AlternatingValue    BaseForAlternatingValue zip
  1                      aaaaaaaaaa            12345
  1                      aaaaaaaaaa            12345
  0                      bbbbbbbbbb            22222
  0                      bbbbbbbbbb            22222
  1                      dddddddddd            33333
  0                      cccccccccc            99999
  </code></pre>
  
  <p>The last AlternatingValue of the last two result rows is different: the Alternating Value needs to alternate between different zip codes. Before it was 0 for the third last row and also 0 for the second last row.</p>
  
  <p>As for Mikael's question below, "And what if you have add row ('cccccccccc','12345'). What would the expected output be then?"</p>
  
  <p>The expected output would then be:</p>
  
  <pre><code>AlternatingValue    BaseForAlternatingValue zip
  1                      aaaaaaaaaa            12345
  1                      aaaaaaaaaa            12345
  0                      cccccccccc            12345
  1                      bbbbbbbbbb            22222
  1                      bbbbbbbbbb            22222
  0                      dddddddddd            33333
  0                      cccccccccc            99999
  </code></pre>
  
  <p>So in summary: I need alternating values for the column BaseForAlternatingValue, but this alternating should be visible when ordering by zip code. (and duplicates in BaseForAlternatingValue need the same "alternating" value)</p>
  
  <h2>----------------</h2>
  
  <p>In the end I found a simpler and relatively nice solution:
  1) using a temp table with an insert into and order by and using id values (id values will reflect the order by clause)
  2) finding out the smallest id for a given BaseForAlternatingValue
  3) finding out the count of distinct BaseForAlternatingValues with an id smaller than that</p>