Mysql – recreate row IDs in sorted table

MySQLsorting

I built word2vec dictionary as a mysql table. There is millions of rows like this:

ID      CREATED                 UPDATED                 KEYWORD     COUNT   NUMPYBLOB   NUMPYBLOB ... ...
121201  2018-03-08 04:01:23     2018-03-08 21:54:34     iulius         32   
121202  2018-03-08 04:01:23     0000-00-00 00:00:00     admonitivo      1   
121203  2018-03-08 04:01:23     0000-00-00 00:00:00     lulius          1   
121204  2018-03-08 04:01:23     2018-03-08 16:56:14     lugh           20   
121205  2018-03-08 04:01:23     2018-03-08 22:34:31     contingencias  66   
121206  2018-03-08 04:01:23     2018-03-08 19:50:34     liberándola    12   
121207  2018-03-08 04:01:23     0000-00-00 00:00:00     agostillo       1   
121208  2018-03-08 04:01:23     2018-03-08 22:47:39     autonomista   212   
121209  2018-03-08 04:01:23     2018-03-08 05:54:12     astrológicamente4
121210  2018-03-08 04:01:23     2018-03-08 18:54:43     redondearse     5

Now I want to sort this table by word occurrence counter (COUNT) and when the table is sorted rewrite autoincremental ID row from zero to X in order to word ocurrency goes down. I can't use COUNT column as primary key, because it is varies continuously and not unique.
How I can do it?

Best Answer

ok, I found a great solution here by adding one more column to my table and label ordered items:

alter table `my_table` add `ocid` bigint after `counter`;
set @neword = 0;
update `my_table` set `ocid` = (@neword := @neword + 1)
order by `counter` desc;

and it's worked:

select id,keyword,counter,ocid from spanish order by `ocid` asc limit 0,15;
+-----+---------+---------+------+
| id  | keyword | counter | ocid |
+-----+---------+---------+------+
|  33 | de      | 6786409 |    1 |
|  43 | ,       | 5633255 |    2 |
|  45 | .       | 4504309 |    3 |
|  41 | la      | 3447327 |    4 |
|  97 | '       | 3014551 |    5 |
|  52 | en      | 2748250 |    6 |
| 120 | el      | 2490996 |    7 |
| 316 | y       | 2200112 |    8 |
|  10 | :       | 1578205 |    9 |
|  28 | que     | 1542144 |   10 |
|   2 | a       | 1494838 |   11 |
|  88 | (       | 1375893 |   12 |
| 220 | )       | 1373788 |   13 |
|   6 | los     | 1290613 |   14 |
|  30 | del     | 1226805 |   15 |
+-----+---------+---------+------+
15 rows in set (1.12 sec)
Related Question