How to get top 3 frequencies in MySQL?
Posted
by Amenhotep
on Stack Overflow
See other posts from Stack Overflow
or by Amenhotep
Published on 2010-03-16T21:03:55Z
Indexed on
2010/03/16
22:11 UTC
Read the original article
Hit count: 296
Hello,
In MySQL I have a table called "meanings" with three columns:
- "person" (int),
- "word" (byte, 16 possible values)
- "meaning" (byte, 26 possible values).
A person assigns one or more meanings to each word:
person word meaning
-------------------
1 1 4
1 2 19
1 2 7 <-- second meaning for word 2
1 3 5
...
1 16 2
Then another person, and so on. There will be thousands of persons.
I need to find for each of the 16 words the top three meanings (with their frequencies). Something like:
word 1: meaning 5 (35% of people), meaning 19 (22% of people), meaning 2 (13% of people)
word 2: meaning 8 (57%), meaning 1 (18%), meaning 22 (7%)
...
Is it possible to solve this with a single MySQL query?
(If this problem is a classic one and has been answered elsewhere, I would appreciate if you could give me a link to the solution.)
Thank you very much,
ve
© Stack Overflow or respective owner