Not able to understand how to impose row_num() without using it through naive sql approach whereas rank and dense_rank works

I have below data set:

mysql> select * from covid_test order by Country, Confirmed;

+------+---------+-----------+
| SNO  | Country | Confirmed |
+------+---------+-----------+
|   19 | China   |         0 |
|    1 | China   |         1 |
|    7 | China   |         2 |
|    9 | China   |         4 |
|   78 | China   |         4 |
|   12 | China   |         5 |
|    3 | China   |         6 |
|   26 | China   |         9 |
|   35 | China   |        10 |
|    2 | China   |        14 |
|    6 | China   |        26 |
|   14 | China   |       444 |
|   77 | India   |        15 |
+------+---------+-----------+

Below queries for rank and dense_rank is working fine however I am not able to get query for row_num, all my strategy fails:

mysql> select ct.*, (select count(distinct Confirmed)+1  from covid_test ct1 where ct1.Country = ct.Country and ct1.Confirmed < ct.Confirmed and ct1.SNO <> ct.SNO) as densernk from covid_test ct order by Country, Confirmed;
+------+---------+-----------+--------+
| SNO  | Country | Confirmed | densernk |
+------+---------+-----------+--------+
|   19 | China   |         0 |      1 |
|    1 | China   |         1 |      2 |
|    7 | China   |         2 |      3 |
|    9 | China   |         4 |      4 |
|   78 | China   |         4 |      4 |
|   12 | China   |         5 |      5 |
|    3 | China   |         6 |      6 |
|   26 | China   |         9 |      7 |
|   35 | China   |        10 |      8 |
|    2 | China   |        14 |      9 |
|    6 | China   |        26 |     10 |
|   14 | China   |       444 |     11 |
|   77 | India   |        15 |      1 |


mysql> select ct.*, (select count(distinct SNO) from covid_test ct1 where ct1.Country = ct.Country and ct1.Confirmed < ct.Confirmed and ct1.SNO <> ct.SNO) as rnk from covid_test ct order by Country, Confirmed;
+------+---------+-----------+------+
| SNO  | Country | Confirmed | rnk  |
+------+---------+-----------+------+
|   19 | China   |         0 |    0 |
|    1 | China   |         1 |    1 |
|    7 | China   |         2 |    2 |
|    9 | China   |         4 |    3 |
|   78 | China   |         4 |    3 |
|   12 | China   |         5 |    5 |
|    3 | China   |         6 |    6 |
|   26 | China   |         9 |    7 |
|   35 | China   |        10 |    8 |
|    2 | China   |        14 |    9 |
|    6 | China   |        26 |   10 |
|   14 | China   |       444 |   11 |
|   77 | India   |        15 |    0 |
+------+---------+-----------+------+

How to achieve row_num for he same set?

Answer

You must also check the condition ct1.Confirmed = ct.Confirmed, in which case you must count the rows with ct1.SNO < ct.SNO:

select ct.*, 
  (
    select count(*) + 1  
    from covid_test ct1 
    where ct1.Country = ct.Country 
      and (
        ct1.Confirmed < ct.Confirmed 
        or (ct1.Confirmed = ct.Confirmed AND ct1.SNO < ct.SNO)
      )  
  ) as row_num 
from covid_test ct 
order by Country, Confirmed;

See the demo.

Advertisement

Answer