Count / Dense_rank grouped sql

Question

Count / Dense_rank grouped sql

Navigation

#1 by (2 votes)

2

I'm trying to group the rows by group in 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 my query returns 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)

sql sql-server query count

asked by anonymous 23.05.2018 / 16:17

1 answer

Texts aligned in the bottom right corner of the screen Error compiling a modular maven project with modular java 10

score 2 · Accepted Answer

If you are using SQL Server as your question indicates, probably the function you want to use is 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 out the result in SQL Fiddle p>

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