Hello readers,
Good morning!

Recently I had a need to create a ranking in MySQL similar to SQL Server's ROW_NUMBER(). Unfortunately, Oracle has not yet implemented this very useful feature, but since everything works out, I'm going to show you how we can implement it.

A very simple way to do this is to create a table and include an auto-increment field, like the example below:

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 line will have its own incremental ID, which can be used as a 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 that query. Can we create a table and store the result of this query in the table using auto increment?
Yes, but it is a very laborious option, especially if many columns are returned in the query.

Populating data so that our table follows the structure below:

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

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 needs by creating the query as follows:

SELECT 
    id,
    nome,
    idade,
    @curRank := @curRank + 1 AS rank 
FROM
    teste p,
    (SELECT @curRank := 0) r 
ORDER BY 
    idade;

And this is the return:

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

Exactly what I needed!

Until next time!