Count/Dense_rank sql grouped

Question

I’m trying to group the lines by group on sqlserver and as a result I want to:

CICLO | CODFIL | CODITPROD | ROW
1     |   1    |     10    | 1
11    |   1    |     10    | 2
12    |   1    |     10    | 3
1     |   2    |     10    | 1
11    |   2    |     10    | 2
1     |   3    |     11    | 1
11    |   3    |     11    | 2

But mine query get back to me

CICLO | CODFIL | CODITPROD | ROW
1     |   1    |     10    | 1
11    |   1    |     10    | 1
12    |   1    |     10    | 1
1     |   2    |     10    | 1
11    |   2    |     10    | 1
1     |   3    |     11    | 1
11    |   3    |     11    | 1

Follow my query

SELECT CICLO,
    CODFIL,
    CODITPROD,
    DENSE_RANK ()
    OVER (PARTITION BY CODFIL, CODITPROD
        ORDER BY CODFIL, CODITPROD )
    AS ROW
FROM TABELA R WITH (NOLOCK)

Answer 1

If you are using SQL Server as your question indicates, probably the function you want to use is the ROW_NUMBER and not DENSE_RANK:

SELECT CICLO,
       CODFIL,
       CODITPROD,
       ROW_NUMBER() OVER (PARTITION BY CODFIL, CODITPROD
                              ORDER BY CODFIL, CODITPROD ) AS ROW
  FROM TABELA R WITH (NOLOCK)

Check the result on SQL Fiddle

---

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

Aplica-se a: SQL Server (do SQL Server 2008 à versão atual), Banco de dados SQL do Windows Azure (da versão inicial até a versão atual).