Revision as of 00:08, 23 January 2010 by imported>mutante (Category:MySQL)
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