How to get the top 3 frequencies in MySQL?

Question

In MySQL, I have a table called "values" with three columns:

• "person" (int),
• "word" (byte, 16 possible values)
• "value" (byte, 26 possible values).

A person assigns one or more meanings to a word to each :

person word meaning
-------------------
1      1    4
1      2    19
1      2    7  <-- Note: second meaning for word 2
1      3    5
...
1      16   2

Then another person and so on. There will be thousands of people.

I need to find for each of the 16 words the three upper meanings (with their frequencies). Sort of:

+--------+-----------------+------------------+-----------------+
| Word   | 1st Most Ranked | 2nd Most Ranked  | 3rd Most Ranked |
+--------+-----------------+------------------+-----------------+
| 1      | meaning 5 (35%) | meaning 19 (22%) | meaning 2 (13%) |
| 2      | meaning 8 (57%) | meaning 1 (18%)  | meaning 22 (7%) |
+--------+-----------------+------------------+-----------------+

...

Is it possible to solve this with a single MySQL query?

Answer 1

, , % , / .

, - /.

, , , , , .

Answer 2

,

 SELECT * FROM words WHERE word = 2 ORDER BY meaning DESC LIMIT 3

, . Im

Answer 3

, , , . @counter.

Answer 4

, , :

+--------+----------------+
| Person | Word | Meaning |
+--------+----------------+
| 1      | 1    | 7       |
| 1      | 1    | 3       |
| 1      | 2    | 8       |
+--------+----------------+

:

+--------+------------------+------------------+-----------------+
| Word   | 1st Most Ranked  | 2nd Most Ranked  | 3rd Most Ranked |
+--------+------------------+------------------+-----------------+
| 1      | meaning 7 (100%) | meaning 3 (100%) | NULL            |
| 2      | meaning 8 (100%) | NULL             | NULL            |
+--------+------------------+------------------+-----------------+

( 50% ):

+--------+------------------+------------------+-----------------+
| Word   | 1st Most Ranked  | 2nd Most Ranked  | 3rd Most Ranked |
+--------+------------------+------------------+-----------------+
| 1      | meaning 7  (50%) | meaning 3  (50%) | NULL            |
| 2      | meaning 8 (100%) | NULL             | NULL            |
+--------+------------------+------------------+-----------------+

Estimated meaning of the frequencies: "How many people think that this value corresponds to this word"?

Thus, this is not just a count of “cases”, but also a count of persons in the table.