title: ROW_NUMBER() with DISTINCT
body: <p>I've got a table of ticket assignments showing the different groups a ticket is transferred to before its resolved. Here is a simplified table:</p>
  
  <pre><code>asgn_grp | date   | ticket_id
  ---------|--------|----------
         A | 1-1-15 | 1
         A | 1-2-15 | 1
         B | 1-3-15 | 1
         A | 1-1-15 | 2
         C | 1-2-15 | 2
         B | 1-3-15 | 2
         C | 1-1-15 | 3
         B | 1-2-15 | 3
  </code></pre>
  
  <p>I need to get a count of the second distinct group that a ticket was assigned to, meaning I want to know once a ticket is transferred out of the group its in, internal transfers don't count. So the second distinct group for <code>ticket 1</code> is <code>B</code>, <code>ticket 2</code> is <code>C</code>, <code>ticket 3</code> is <code>B</code>. I need to get a count of these, so the end result I need is</p>
  
  <pre><code>asgn_grp | count
  ---------|-------
         B | 2
         C | 1
  </code></pre>
  
  <p>I've tried </p>
  
  <pre><code>SELECT distinct top 2 asgn_grp, ROW_NUMBER() OVER (ORDER BY date) 
  </code></pre>
  
  <p>As my sub-query and pulling the second one out of that, but when I add the <code>ROW_NUMBER()</code> it messes up my distinct. If I pull the <code>ROW_NUMBER()</code> out of the sub-query, I have now way to order my values to ensure I get the second one after I <code>DISTINCT</code> the list.</p>
  
  <p>Also, let me know if I was unclear about anything.</p>