Developing Queries with Ranking in MySQL

Views: 2.432
Reading Time: 2 minutes

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:

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:

We can meet our need by creating the query as follows:

And this is the return:

Just what I needed!

To the next!