SQL Server : query for DENSE_RANK() grouped by a column? -

need constructing sql statement scenario, on orders table, holds orderid, customerid, itemid , other misc. columns.

say table looks like:

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 

what i'd have additional column is, 1 counter increments each time new customerid begins, , loops on counting items customer purchased. i'm using dense_rank() , able first counter, how handle second?

select      dense_rank() on (order customerid) counter,     *     orders order     customerid asc 

this gives me:

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 

and finally, want counter2 column added somehow:

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 

you can use row_number() second counter:

select      dense_rank() on (order customerid) counter,     row_number() over(partition customerid order orderid) counter2,     *     orders order     customerid asc; 

see sql fiddle demo. result is:

| 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 |