title: Count number of repeats in SQL
body: <p>I tried to solve one problem but without success.</p>
  
  <p>I have two list of number</p>
  
  <pre><code>{1,2,3,4}
  {5,6,7,8,9}
  </code></pre>
  
  <p>And I have table</p>
  
  <pre><code>ID     Number
  1        1
  1        2
  1        7
  1        2
  1        6
  2        8 
  2        7
  2        3
  2        9
  </code></pre>
  
  <p>Now I need to count how many times number from second list come after number from first list but I should count only one by one id</p>
  
  <p>in example table above result should be 2</p>
  
  <p>three matched pars but because we have only two different IDs result is 2 instead 3</p>
  
  <p>Pars:</p>
  
  <pre><code>1     2
  1     7
  </code></pre>
  
  <hr>
  
  <pre><code>1     2
  1     6
  </code></pre>
  
  <hr>
  
  <pre><code>2     3
  2     9
  </code></pre>
  
  <p>note. I work with MSSQL</p>
  
  <p>Edit. There is one more column Date which determined order</p>
  
  <p>Edit2 - Solution</p>
  
  <p>i write this  query</p>
  
  <pre><code>SELECT * FROM table t
  left JOIN table tt ON tt.ID = t.ID 
      AND tt.Date &gt; t.Date 
      AND t.Number IN (1,2,3,4) 
      AND tt.Number IN (6,7,8,9) 
  </code></pre>
  
  <p>And after this I had a plan to group by id and use only one match for each id but execution take a lot time</p>