Mysql – How to find Rank of row in MySQL

MySQLrank

I am new to MySQL. I know that ROW_NUMBER() OVER (PARTITION) will not work in MySQL. I want to convert the code from SQL Server to MySQL:

SELECT  *, 
    ROW_NUMBER() OVER (
        Partition By X.TPID,originTLA, destinationTLA  
        ORDER BY packageStartDate,totalPackageSavingsPct desc
    ) AS ttl_perct_rnk 
FROM TABLE

I want to convert this code to MySQL.

In Most of the cases the requirement to find rank is based on a single column. But here we have to find rank based on three columns. It is easy to find rank based on single column. But I am not sure how can we implement the rank based on three column or multiple columns.

Best Answer

This what you are looking for?:

SELECT  @rank := @rank + 1 AS 'Rank', province, city, population
    FROM  canada
    JOIN  ( SELECT  @rank := 0 ) AS init
    WHERE  population > 0
    ORDER BY  province, city, population DESC;


+------+----------+---------------+------------+
| Rank | province | city          | population |
+------+----------+---------------+------------+
|    1 | Alberta  | Airdrie       |      24673 |
|    2 | Alberta  | Athabasca     |       2539 |
|    3 | Alberta  | Banff         |       7502 |
|    4 | Alberta  | Barrhead      |       4430 |
|    5 | Alberta  | Bassano       |       1388 |
|    6 | Alberta  | Beaverlodge   |       2219 |
|    7 | Alberta  | Black Diamond |       3663 |
|    8 | Alberta  | Blackfalds    |       3198 |
|    9 | Alberta  | Blairmore     |       2095 |
|   10 | Alberta  | Bon Accord    |       1611 |
|   11 | Alberta  | Bonnyville    |       6003 |
|   12 | Alberta  | Bow Island    |       1792 |
|   13 | Alberta  | Brooks        |      12745 |
|   14 | Alberta  | Calgary       |     968475 |
|   15 | Alberta  | Calmar        |       2000 |
|   16 | Alberta  | Camrose       |      15687 |
...
|  708 | Saskatchewan              | Wilkie                    |       1198 |
|  709 | Saskatchewan              | Wynyard                   |       1793 |
|  710 | Saskatchewan              | Yorkton                   |      15172 |
|  711 | Yukon                     | Haines Junction           |       1148 |
|  712 | Yukon                     | Mayo                      |        540 |
|  713 | Yukon                     | Whitehorse                |      19616 |
+------+---------------------------+---------------------------+------------+

Or you can upgrade to MySQL 8.0 or MariaDB 10.2 to get CTEs and Windowing functions.

Related Question