0

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.