Retornando sequência numérica em uma consulta SQL

O que devemos fazer para que cada registro do resultado de uma consulta SQL possua um campo sequencial ordenado? Essa é uma das perguntas mais simples de resposta mais complicada que conheço a respeito de SQL. Uma das maiores complicações é que a solução muda dramaticamente de banco para banco. Para exemplificar esse problema, vamos imaginar uma tabela clientes, com campos codigo e nome. Queremos que essa tabela seja listada e ordenada por nome, daí executamos:


SELECT codigo, nome FROM clientes ORDER BY nome


E temos o seguinte resultado:


codigo      nome
----------- -----------------------------
482496      AMERCIA MED
4120        AQUARIUS
30528       C NORDESTINO
30530       C PONTE CARV
478919      COM PARANHOS
6131        COM.ITAMBE
485327      CRISTIANE DA
485866      D ARAUJO & S
482649      D VERDE MAR
30529       DIAMANTE C A
483240      E C SILVA G
4580        EDNILSON P
4454        FERREIRA COS
481520      GD LIMA INF
29077       LAJEDO 1
29078       LAJEDO 2
23976       LJ CONDOMINI
480728      LOPES & FONS
30325       M NORDESTIN
43297       N PAES MELO
480350      TUTTO LIMP
29081       VAREJAO BRA


(22 registros)


Agora gostaríamos de adicionar uma coluna nessa consulta que retornasse o número da linha, de 1 à 22, seguindo a mesma sequencia de ordenação da consulta, da sequinte forma:


sequencia   codigo      nome
----------- ----------- -----------------------------
1           482496      AMERCIA MED
2           4120        AQUARIUS
3           30528       C NORDESTINO
4           30530       C PONTE CARV
5           478919      COM PARANHOS
6           6131        COM.ITAMBE
7           485327      CRISTIANE DA
8           485866      D ARAUJO & S
9           482649      D VERDE MAR
10          30529       DIAMANTE C A
11          483240      E C SILVA G
12          4580        EDNILSON P
13          4454        FERREIRA COS
14          481520      GD LIMA INF
15          29077       LAJEDO 1
16          29078       LAJEDO 2
17          23976       LJ CONDOMINI
18          480728      LOPES & FONS
19          30325       M NORDESTIN
20          43297       N PAES MELO
21          480350      TUTTO LIMP
22          29081       VAREJAO BRA
 
Bom, como disse anteriormente, para cada banco de dados, teremos uma solução diferente. Por isso, descreverei a solução em alguns bancos que conheço. Estou descrevendo por ordem de complicação, pois dessa forma, evitamos maiores sequelas...

Oracle
Dizer que a solução é mais simples com Oracle pode assustar aqueles que não conhecem bem o Oracle. Mas a verdade é que muitas coisas que são complicadas em outros bancos apresentam uma solução bem mais simples com o Oracle. Neste caso, o Oracle tem uma função "ROW_NUMBER()", que aplica um número único a cada registro de uma consulta. E isso é exatamente o que precisamos! Eis a consulta que retorna o que precisamos no Oracle:

SELECT ROW_NUMBER() OVER (ORDER BY nome) AS sequencia, codigo, nome FROM clientes ORDER BY nome

Percebam que a função ROW_NUMBER possui uma claúsula "OVER" que nos permite determinar sobre qual critério queremos gerar o seguencial. Maiores detalhes a respeito dessa função, consulte a documentação em: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions122.htm

Microsoft SQL Server (versão 2005 ou superior)
Por mais incrível que pareça, o SQL Server, a partir de versão 2005, também possui uma função ROW_NUMBER(), inclusive com a mesma sintaxe do Oracle! E como essa função já retorna aquilo que precisamos, a solução no SQL Server fica simples também (desde que a versão do banco seja igual ou maior à 2005).

SELECT ROW_NUMBER() OVER (ORDER BY nome) AS sequencia, codigo, nome FROM clientes ORDER BY nome

Da mesma forma como no Oracle, a função ROW_NUMBER possui uma claúsula "OVER" que nos permite determinar sobre qual critério queremos gerar o seguencial. Maiores detalhes a respeito dessa função, consulte a documentação da Microsoft, que é um pouco diferente da Oracle, mas igualmente bem detalhada: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Se seu banco de dados for SQL Server 2000, sugiro fortemente instalar uma versão superior, como a 2005 ou a 2008. Agora, se não for possível fazer essa atualização, você pode chorar, ou tentar a alternativa que descrevo mais abaixo (outras pessoas já choraram muito até chegarem a essa solução).

SQLite
Em SQLite, nao existe uma forma direta de se fazer isso. Utilizamos para tanto, um artifício baseado em uma característica muito interessante do SQLite: Todos os registros de qualquer tabela possuem um rowid, que é um sequencial numérico do registro que segue a sequência de inserção nessa tabela. Dessa forma, podemos criar uma tabela temporária, construída a partir da consulta que queremos adicionar o sequencial. Se inserirmos os registros na ordem desejada, cada um receberá o rowid equivalente ao sequencial que queremos. Em SQLite, você pode facilmente criar uma tabela a partir de uma consulta.

CREATE TEMP TABLE tmp_clientes AS SELECT codigo, nome FROM clientes ORDER BY nome;

Note que a tabela é criada a partir do mesmo select anterior, mantendo-se inclusive a ordenação. Note também o "TEMP" na instrução SQL. Isso indica que a tabela criada é temporária e não será visível para outras conexões. A partir de agora, temos uma tabela temporária, com os registros desejados, e com um rowid equivalente ao sequencial que queremos. Ou seja, basta fazermos a consulta:

SELECT rowid AS sequencia, codigo, nome FROM tmp_clientes ORDER by nome;

É importante, apagar a tabela temporária após utilizar o retorno da consulta. Em SQLite, as tabelas temporárias existem apenas para a conexão que as criou, e desaparecerão quando a conexão for encerrada, mas antes disso, podem gerar erros caso tente-se criar novamente a mesma tabela.

Microsoft SQL Server (versões anteriores à 2005)
Não recebo nenhum tipo de comissão da Microsoft, mas mesmo assim, acho interessante insistir: faça o possível para atualizar seu SQL Server, pois as versões mais recentes, além de disponibilizarem uma maior variedade de recursos, ainda têm um ganho considerável de desempenho em relação as versões anteriores à 2005.
Se mesmo assim, você quiser implementar essa solução no seu banco SQL Server anterior à versão 2005, segue a solução:

A solução em versões antigas do SQL Server, é semelhante ao SQLite, sendo que mais complicado. Primeiro porque o SQL Server não gera um "rowid" automaticamente. Segundo, porque não é possível criar uma tabela a partir de uma consulta como no SQLite. Por isso precisaremos criar uma tabela com os campos necessários, e adicionar um campo identity para gerar o sequencial. Podemos também criar a tabela como temporária, que em SQL Server, basta nomear a tabela iniciando dom "#".

CREATE TABLE #tmp_clientes (
    sequencia   INTEGER IDENTITY(1,1),
    codigo      INTEGER,
    nome        VARCHAR(50)
);

Depois de criar a tabela, inserimos os registros a partir da mesma consulta que queremos adicionar o sequencial. O campo do tipo identity gera um sequencial automaticamente. Se inserirmos os registros na ordem desejada, cada um receberá o sequencial da forma como queremos.

INSERT INTO #tmp_clientes(codigo, nome) SELECT codigo, nome FROM clientes ORDER BY nome;

com isso, temos uma tabela temporária, com os registros desejados, e com o campo sequencia que queremos. Ou seja, basta fazermos a consulta:

SELECT sequencia, codigo, nome FROM #tmp_clientes ORDER by nome;

Mais uma vez lembrando que é importante, apagar a tabela temporária após utilizar o retorno da consulta. Da mesma forma como no SQLite, as tabelas temporárias no SQLServer existem apenas para a conexão que as criou, e desaparecerão quando a conexão for encerrada, mas antes disso, podem gerar erros caso tente-se criar novamente a mesma tabela.

Firebird
Em Firebird, precisaremos criar usar um generator ou um sequence. O generator funciona em qualquer versão do Firebird. Já a partir da versão 2.0, foi introduzido o sequence que é a implementação recomendada para geração de números sequenciais. Resumindo: De preferência, deve-se usar o sequence, mas se for necessário criar códigos compativeis com versões anteriores ao Firebird 2.0, deve ser usado o generator.

Para criar um generator, usamos:

CREATE GENERATOR TMP_GEN;

Para criar um sequence:

CREATE SEQUENCE TMP_SEQUENCE;

A partir daí, a tarefa fica simples. Basta executar a consulta adicionando o campo sequencial. Para generator:

SELECT GEN_ID(TMP_GEN, 1) as sequencia, codigo, nome FROM clientes order by nome;

Para sequence:

SELECT NEXT VALUE FOR TMP_SEQUENCE as sequencia, codigo, nome FROM ta101 order by nome;

Pra finalizar, devemos apagar o generator ou o sequence. Dessa forma, deixamos tudo da mesma forma como encontrado. De certa forma, a implementação fica suja, porque não temos como criar esse sequencial de forma temporária. É preciso ter cuidado para não executar essa mesma operação ao mesmo tempo em duas conexões distintas. Se possível, é recomendado envolver a operação dentro de uma transação. Isso garante a unicidade da operação.

Outros bancos

Listei aqui as soluções nos bancos de dados em que tenho mais experiência. Me lembro de ter feito algo semelhante no PostgresSQL, mas não me lembro exatamente como fiz. Sei que a solução é uma variação de uma dessas que apresentei aqui. E provavelmente em outros bancos, será algo do tipo também. Se alguém conhecer algum solução para outro banco de dados, ou uma solução melhor do que as apresentadas aqui, enviem-me que terei prazer em atualizar esse post.