SQL to generate sequence number within each group
<pre>
 PO NO     Acc No    ResultSeqNo
    A1           12          1 
    A1           12          1
    A1           12          1
    A1           12          1
    A2           12          2 
    A2           12          2
    A2           12          2
    A3           13          1
    A3           13          1
    A4           13          2 
    A4           13          2
    A5           14          1 
</pre>

<p>In above table, <code>PONO</code> and <code>AccNo</code> are related, <code>SeqNo</code> is nothing but the Counter in the flag. I need to group by <code>PONo</code> and <code>Accno</code>. </p>

<p>If you see in the above table, <code>A1</code> and <code>A2</code> has same account number , but all <code>A1</code> is considered to be a single group, so the seqNo should be 1 for all <code>A1</code>. 
<code>A2</code> also has the same acc no as <code>A1</code>, but it's a different group, so it should have the seq No 2. </p>

<p>Put another way: for each <code>AccNo</code> number, I need to generate sequence numbers by <code>PO NO</code>. So for Account #12, all of the <code>A1</code> PO NO records have sequence 1, and all of the <code>A2</code> PO NO records have sequence 2. For account 13, A3 is 1 and A4 is 2. </p>

<p>Here is what I have so far:</p>

<pre><code>UPDATE x 
SET x.ResultSeqNo = x.dr 
FROM 
  (
    SELECT SeqNo,dense_rank() over (order by PO NO) as dr 
    FROM [Purchaseorder] 
    WHERE status = 'In'
  ) x
</code></pre>