You need a query to find out the most common answers to questions:
SELECT
question_id,
answer_id,
COUNT(*) as cnt
FROM user_answers
GROUP BY 1, 2
ORDER BY 1, 3 DESC
This would return a table where for each question_id we output counts in descending order.
| 1 | 1 | 3 |
| 1 | 2 | 1 |
| 2 | 10 | 3 |
| 2 | 11 | 1 |
And now we should solve a so called greatest-n-per-group task. The problem is that in MySQL for the sake of performance the tasks like this are usually solved not in pure SQL, but using hacks which rest on knowledge how the queries are processed internally.
In this case we know that we can define a variable and then iterating over the ready table, have knowledge about the previous row, which allows us to distinguish between the first row in a group and the others.
SELECT
question_id, answer_id, cnt,
IF(question_id=@q_id, NULL, @q_id:=question_id) as v
FROM (
SELECT
question_id, answer_id, COUNT(*) as cnt
FROM user_answers
GROUP BY 1, 2
ORDER BY 1, 3 DESC) cnts
JOIN (
SELECT @q_id:=-1
) as init;
Make sure that you have initialised the variable (and respect its data type on initialisation, otherwise it may be unexpectedly casted later). Here is the result:
| 1 | 1 | 3 | 1 |
| 1 | 2 | 1 |(null)|
| 2 | 10 | 3 | 2 |
| 2 | 11 | 1 |(null)|
Now we just need to filter out rows with NULL in the last column. Since the column is actually not needed we can move the same expression into the WHERE clause. The cnt column is actually not needed either, so we can skip it as well:
SELECT
question_id, answer_id
FROM (
SELECT
question_id, answer_id
FROM user_answers
GROUP BY 1, 2
ORDER BY 1, COUNT(*) DESC) cnts
JOIN (
SELECT @q_id:=-1
) as init
WHERE IF(question_id=@q_id, NULL, @q_id:=question_id) IS NOT NULL;
The last thing worth mentioning, for the query to be efficient you should have correct indexes. This query requires an index starting with (question_id, answer_id) columns. Since you anyway need a UNIQUE index, it make sense to define it in this order: (question_id, answer_id, user_id).
CREATE TABLE user_answers (
user_id INTEGER,
question_id INTEGER,
answer_id INTEGER,
UNIQUE INDEX (question_id, answer_id, user_id)
) engine=InnoDB;