Hello readers,
Good Morning!
I recently had a need to create a MySQL ranking similar to SQL Server ROW_NUMBER (). Unfortunately, Oracle has not yet implemented this feature as useful, but as if everything works out, I will show you how we can implement this.
A very simple way to do this is to create a table and include an auto-increment field like the example below:
| 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`) ); |
This way each row will have its own incremental ID, which can be used as Ranking.
The problem is when we need to create slightly more complex queries using joins and filters and we need to create a ranking in this query. Can we create a table and store the result of this query in the table using an auto increment?
Yes, but it's a pretty cumbersome option, especially if many columns are returned in the query.
Populating data so that our table is as follows:
| 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); |
We can meet our need by creating the query as follows:
| 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; |
And this is the return:
| 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 |
Just what I needed!
To the next!
Good morning Dirceu!
Very good the post, worked well. However the problem is that it is not possible to filter the results by the rank column in the WHERE clause as it results as an unknown column.
Example:
SELECT
id,
name,
age,
@curRank: = @curRank + 1 AS rank
FROM
p test,
(SELECT @curRank: = 0) r
ORDER BY
age
WHERE
rank BETWEEN 1 AND 5 || name = 'Bartolomeu'
Results in an error like “# 1054 - Unknown column 'rank' in 'where clause'”
Hugs !!
Danilo,
Good night!
First, thanks for stopping by!
To filter using the Ranking you created, you can use subqueries:
SELECT *
FROM (
SELECT
id,
name,
age,
@curRank: = @curRank + 1 AS rank
FROM
p test,
(SELECT @curRank: = 0) r
ORDER BY
age
) AT
WHERE
A.rank> = 5