Olá leitores,
Bom dia!
Recentemente tive uma necessidade de criar um ranking no MySQL semelhante ao ROW_NUMBER() do SQL Server. Infelizmente, a Oracle ainda não implementou esse recurso tão útil, mas como se tudo dá um jeito, vou mostrar a vocês como podemos implementar isso.
Uma forma bem simples de fazer isso, é criar uma tabela e incluir um campo de auto incremento, como o exemplo abaixo:
|
1 2 3 4 5 6 7 8 |
DROP TABLE IF EXISTS `teste`; CREATE TABLE `teste`( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `nome` VARCHAR(50) NOT NULL, `idade` INT(11) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ); |
Dessa forma, cada linha terá o seu próprio ID incremental, que pode ser usado como Ranking.
O problema é quando precisamos criar queries um pouco mais complexas utilizando com joins e filtros e precisamos criar um ranking nessa query. Podemos criar uma tabela e guardar o resultado dessa query na tabela usando um auto incremento?
Sim, mas é um opção bem trabalhosa, principalmente se muitas colunas forem retornadas na query.
Populando dados de forma que a nossa tabela fique conforme a estrutura abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
id nome idade ------ ---------------- -------- 1 João Zebedeu 27 2 Pedro 32 3 Mateus 15 4 Judas Tadeu 29 5 André 64 6 Tomé 60 7 Filipe 25 8 Bartolomeu 47 9 Tiago 28 10 Tiago 33 11 Judas Iscariotes 666 12 Simão 39 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO `teste` (nome, idade) VALUES('João Zebedeu', 27), ('Pedro', 32), ('Mateus', 15), ('Judas Tadeu', 29), ('André', 64), ('Tomé', 60), ('Filipe', 25), ('Bartolomeu', 47), ('Tiago', 28), ('Tiago', 33), ('Judas Iscariotes', 666), ('Simão', 39); |
Podemos atender a nossa necessidade criando a query da seguinte forma:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT id, nome, idade, @curRank := @curRank + 1 AS rank FROM teste p, (SELECT @curRank := 0) r ORDER BY idade; |
E esse é o retorno:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
id nome idade rank ------ ---------------- ------ -------- 3 Mateus 15 1 7 Filipe 25 2 1 João Zebedeu 27 3 9 Tiago 28 4 4 Judas Tadeu 29 5 2 Pedro 32 6 10 Tiago 33 7 12 Simão 39 8 8 Bartolomeu 47 9 6 Tomé 60 10 5 André 64 11 11 Judas Iscariotes 666 12 |
Exatamente o que eu precisava!
Até a próxima!
Bom dia Dirceu!
Muito bom o post, funcionou direitinho. Contudo o problema é que não é possível filtrar os resultados pela coluna rank na cláusula WHERE, pois resulta como coluna desconhecida.
Exemplo:
SELECT
id,
nome,
idade,
@curRank := @curRank + 1 AS rank
FROM
teste p,
(SELECT @curRank := 0) r
ORDER BY
idade
WHERE
rank BETWEEN 1 AND 5 || nome=’Bartolomeu’
Resulta em erro do tipo “#1054 – Unknown column ‘rank’ in ‘where clause'”
Abraços!!
Danilo,
Boa noite!
Primeiramente, obrigado pela visita!
Para filtrar utilizando o Ranking que você criou, pode-se utilizar subqueries:
SELECT *
FROM (
SELECT
id,
nome,
idade,
@curRank := @curRank + 1 AS rank
FROM
teste p,
(SELECT @curRank := 0) r
ORDER BY
idade
) A
WHERE
A.rank >= 5