Criando queries com uma coluna RecordNumber (ranking)

A ABC71 disponibiliza para seus Clientes uma ferramenta para construção de relatórios baseada no Report Builder, onde se monta relatórios costurando queries prontas a um layout de apresentação. Outro dia precisei montar uma query para um relatório desses e era imperativo que uma das colunas trouxesse valores sequenciais (um ranking) de modo que no primeiro registro teria valor 1, no 2o teria valor 2 e assim por diante.

Pesquisei no help do SQL Server 2005 e me deparei com uma solução talhada para resolver esse tipo de problema já que a Microsoft incluiu funções para ranking nessa versão! Para o que eu precisava, a função ROW_NUMBER bastava. O uso dela é na cláusula SELECT como no exemplo:

SELECT EMP_FIL, FORNEC, ROW_NUMBER() OVER (ORDER BY FORNEC) AS SEQUENCIA
FROM CFOR
ORDER BY EMP_FIL

A cláusula OVER que vem depois do nome da função é obrigatória e indica qual é o campo (ou campos) que deve(m) ser usado(s) para sequenciar os registros. A grosso modo, a query é primeiro ordenada segundo as instruções dessa cláusula, um número sequencial é atribuído a cada registro e só então o ORDER BY geral da query é aplicado antes dos registros serem retornados.

Como não é obrigatório que o ORDER BY estipulado no OVER seja o mesmo da query, pode ser que a coluna com o ranking não venha na ordem.

Essa solução seria perfeita se ABC71 não tivesse Clientes que usassem outros tipos de banco de dados, como ORACLE, PostgreSQL e versões mais antigas do SQL Server. E como esses comandos não são do padrão SQL ....

Pesquisando na Internet, encontrei outras sugestões para solucionar esse problema numa gama maior de versões e fabricantes de bancos de dados. A que me pareceu mais consistente sugere criar um campo IDENTITY na tabela que deve ser rankeada, sendo o campo programado para iniciar com valor 1 e para ir incrementando de 1 em 1 a cada novo registro inserido. O trecho de script que segue é um exemplo em SQL Server para criar uma tabela cuja coluna ID tem essas características:

CREATE TABLE [dbo].[CFOR](
ID identity(1, 1),
[EMP_FIL] [int] NOT NULL,
[FORNEC] [numeric](14, 0) NOT NULL,
[RAZ_SOCIAL] [varchar](35) NULL,
[NOME_FANTAS] [varchar](25) NULL
)

Para quem já tem muitos Clientes usando uma aplicação com a tabela, no entanto, essa abordagem ainda pode ser inconveniente pois será necessário reorganizar a tabela para incluir o novo campo.

Uma solução que deve ser aplicável a todos os bancos relacionais envolve criar um OUTER JOIN já que esse é um recurso padrão SQL. A ideia é montar uma query agrupando (GROUP BY) todas as colunas desejadas e ligando essa query a uma outra que envolva a mesma tabela. A ligação é através de LEFT OUTER JOIN, usada para trazer todos os registros da primeira query e usando a segunda para contar quantos registros são maiores ou iguais ao registro atual. O comando reproduzido abaixo traz o mesmo resultado da primeira query deste post. Tal comando está usando a sintaxe do SQL Server.

SELECT F1.EMP_FIL, F1.FORNEC, COUNT(*) AS SEQUENCIA
FROM CFOR F1 LEFT OUTER JOIN
CFOR F2 ON (F1.FORNEC > F2.FORNEC)
OR (F1.FORNEC = F2.FORNEC AND F1.EMP_FIL >= F2.EMP_FIL)
GROUP BY F1.FORNEC, F1.EMP_FIL
ORDER BY F1.FORNEC, F1.EMP_FIL

Já que nem tudo pode ser perfeito, esta query resolve o problema para diversos bancos mas tem um tempo de execução muito maior que a primeira, usando ROW_NUMBER. Com ROW_NUMBER, executou praticamente instantâneo para 23.000 registros enquanto a que usa LEFT OUTER JOIN levou quase 2 minutos no mesmo servidor.

Mais Informações
Funções de Ranking para SQL Server 2005 e posteriores, colunas do tipo INDENTITY