Wikistats/rank

query that calculates the rank within mysql:

mysql> SELECT v1.prefix, v1.good, COUNT(v2.good) AS Rank FROM wiktionaries v1 JOIN wiktionaries v2 ON v1.good < v2.good OR (v1.good=v2.good and v1.prefix = v2.prefix) GROUP BY v1.prefix, v1.good ORDER BY v1.good DESC, v1.prefix DESC;

+------------+---------+------+
| prefix     | good    | Rank |
+------------+---------+------+
| fr         | 1570222 |    1 |
| en         | 1562659 |    2 |
| lt         |  407265 |    3 |
| tr         |  266041 |    4 |
| zh         |  252700 |    5 |
| ru         |  233132 |    6 |
| vi         |  228403 |    7 |
| io         |  164196 |    8 |
| pl         |  145690 |    9 |
| el         |  143529 |   10 |
| fi         |  138091 |   11 |
| hu         |  134541 |   12 |
| no         |  123168 |   13 |
| ta         |  104478 |   14 |
| it         |  102817 |   15 |
| de         |  101149 |   16 |
| pt         |   99506 |   17 |
| sv         |   96828 |   18 |
| ko         |   88217 |   19 |
| nl         |   65993 |   20 |
| lo         |   60653 |   21 |
| ja         |   49056 |   22 |
| ku         |   48111 |   23 |
| ar         |   47408 |   24 |
| kn         |   42800 |   25 |
| es         |   42335 |   26 |
| ro         |   37434 |   27 |
| et         |   35740 |   28 |
| id         |   34875 |   29 |
| te         |   34716 |   30 |
| gl         |   28682 |   31 |
| bg         |   26345 |   32 |
| uk         |   24701 |   33 |
| ca         |   24075 |   34 |
| vo         |   22045 |   35 |
| ml         |   21371 |   36 |
| is         |   16587 |   37 |
| fa         |   16462 |   38 |
| li         |   16281 |   39 |
| sr         |   15240 |   40 |
| af         |   14546 |   41 |
| cs         |   13610 |   42 |
| sw         |   12997 |   43 |
| fy         |   12738 |   44 |
| th         |   12682 |   45 |
| scn        |   12669 |   46 |
| simple     |   12561 |   47 |
| br         |   12074 |   48 |
| oc         |   11635 |   49 |
| he         |    9818 |   50 |
| sl         |    7110 |   51 |
| hy         |    6995 |   52 |
| sq         |    6714 |   53 |
| tt         |    6643 |   54 |
| la         |    6402 |   55 |
| hr         |    6292 |   56 |
| zh-min-nan |    5922 |   57 |
| da         |    5516 |   58 |
| tk         |    4231 |   59 |
| ast        |    4230 |   60 |
| wa         |    4053 |   61 |
| ur         |    3839 |   62 |
| hsb        |    3666 |   63 |
| kk         |    3514 |   64 |
| ky         |    3261 |   65 |
| wo         |    2689 |   66 |
| eo         |    2251 |   67 |
| ang        |    2194 |   68 |
| hi         |    1840 |   69 |
| ga         |    1707 |   70 |
| gn         |    1698 |   71 |
| az         |    1680 |   72 |
| ia         |    1672 |   73 |
| co         |    1619 |   74 |
| sk         |    1430 |   75 |
| csb        |    1393 |   76 |
| st         |    1387 |   77 |
| ms         |    1360 |   78 |
| mr         |    1304 |   79 |
| ka         |    1294 |   80 |
| tl         |    1260 |   81 |
| ne         |    1219 |   82 |
| nds        |    1033 |   83 |
| kl         |    1023 |   84 |
| sd         |     840 |   85 |
| ug         |     824 |   86 |
| ti         |     751 |   87 |
| eu         |     694 |   88 |
| mk         |     666 |   89 |
| cy         |     638 |   90 |
| ps         |     582 |   91 |
| an         |     576 |   92 |
| sh         |     531 |   93 |
| my         |     530 |   94 |
| lv         |     512 |   95 |
| bn         |     510 |   96 |
| gu         |     480 |   97 |
| mg         |     429 |   98 |
| km         |     428 |   99 |
| ts         |     358 |  100 |
| qu         |     350 |  101 |
| bs         |     329 |  102 |
| fo         |     323 |  103 |
| am         |     316 |  104 |
| rw         |     306 |  105 |
| chr        |     247 |  106 |
| su         |     236 |  107 |
| om         |     203 |  108 |
| mn         |     202 |  109 |
| nah        |     194 |  110 |
| ie         |     183 |  111 |
| yi         |     178 |  112 |
| be         |     171 |  113 |
| iu         |     167 |  114 |
| gd         |     141 |  115 |
| tg         |     135 |  116 |
| nn         |     133 |  117 |
| si         |     132 |  118 |
| zu         |     128 |  119 |
| pa         |     122 |  120 |
| dv         |     120 |  121 |
| mt         |     119 |  122 |
| kw         |     114 |  123 |
| tpi        |     110 |  124 |
| gv         |     108 |  125 |
| roa-rup    |     101 |  126 |
| mi         |      92 |  127 |
| sg         |      91 |  128 |
| jv         |      90 |  129 |
| uz         |      86 |  130 |
| ik         |      82 |  131 |
| so         |      71 |  132 |
| ha         |      59 |  133 |
| ss         |      46 |  134 |
| sa         |      43 |  135 |
| na         |      41 |  136 |
| ay         |      41 |  136 |
| jbo        |      31 |  138 |
| tn         |      22 |  139 |
| sm         |      21 |  140 |
| as         |      21 |  140 |
| lb         |      17 |  142 |
| ks         |      16 |  143 |
| fj         |      14 |  144 |
| ln         |      12 |  145 |
| mo         |      11 |  146 |
| za         |       8 |  147 |
| pi         |       6 |  148 |
| xh         |       5 |  149 |
| mh         |       5 |  149 |
| ba         |       5 |  149 |
| sn         |       4 |  152 |
| or         |       4 |  152 |
| bh         |       4 |  152 |
| ak         |       4 |  152 |
| yo         |       3 |  156 |
| rn         |       3 |  156 |
| bm         |       3 |  156 |
| bi         |       3 |  156 |
| av         |       3 |  156 |
| to         |       2 |  161 |
| ab         |       2 |  161 |
| aa         |       2 |  161 |
| tw         |       1 |  164 |
| dz         |       1 |  164 |
| tokipona   |       0 |  166 |
| sc         |       0 |  166 |
| rm         |       0 |  166 |
| cr         |       0 |  166 |
| ch         |       0 |  166 |
| bo         |       0 |  166 |
| als        |       0 |  166 |
+------------+---------+------+
172 rows in set (0.03 sec)

From http://www.artfulsoftware.com/infotree/queries.php?&bw=1024#460