title: SQL Server : query for DENSE_RANK() grouped by a column?
body: <p>Need help constructing a SQL statement for this scenario, over an <code>orders</code> table, that holds the <code>orderID, customerID, itemID</code> and other misc. columns.</p>
  
  <p>Say the table looks like:</p>
  
  <pre><code>OrderID CustomerID ItemID Details
  1       1234       543    abc
  2       1234       643    xxx
  3       1234       743    try
  4       5678       743    try
  5       5678       999    iuy
  6       5678       643    xxx
  </code></pre>
  
  <p>What I'd like to have as an additional column is, one counter that increments each time a new <code>customerID</code> begins, and another that loops over counting the items the customer purchased. I'm using <code>DENSE_RANK()</code> and I am able to do the first counter, but how do I also handle the second?</p>
  
  <pre><code>SELECT 
      DENSE_RANK() OVER (ORDER BY CustomerID) as Counter,
      *
  FROM
      Orders
  ORDER BY
      CustomerID ASC
  </code></pre>
  
  <p>This gives me:</p>
  
  <pre><code>Counter OrderID CustomerID ItemID Details
  1       1       1234       543    abc
  1       2       1234       643    xxx
  1       3       1234       743    try
  2       4       5678       743    try
  2       5       5678       999    iuy
  2       6       5678       643    xxx
  </code></pre>
  
  <p>And finally, what I WANT is the <code>Counter2</code> column added somehow:</p>
  
  <pre><code>Counter Counter2 OrderID CustomerID ItemID Details
  1       1        1       1234       543    abc
  1       2        2       1234       643    xxx
  1       3        3       1234       743    try
  2       1        4       5678       743    try
  2       2        5       5678       999    iuy
  2       3        6       5678       643    xxx
  </code></pre>