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.
