MySQL DELETE row ORDER BY COUNT DESC

Question

I have a table that contains the similarity of every two questions of a test bank.

This means question_id 6 is 84% similar to question_id 10. And there are 12 similar question of question_id 6.

I just what the most relevant questions, or the top 7 relevant questions.

I have seen Mysql delete order by and tried:

DELETE FROM     exam_relatedquestion
WHERE
    `exam_relatedquestion`.id IN (
        SELECT
            `exam_relatedquestion`.id
        FROM
            (
                SELECT  `exam_relatedquestion`.id
                FROM    `exam_relatedquestion`
                GROUP BY
                    `exam_relatedquestion`.from_question_id_id
                ORDER BY
                    `exam_relatedquestion`.similarity DESC
                LIMIT 7
            ) a
    )

But the error message is:

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'den.exam_relatedquestion.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How can I delete any rows that are not the top 7 relevant of a question?

Answer 1

That won't work. Your pseudo-code is not correct anyway, because the sort is in the wrong direction.

In any case, you can use variables to enumerate the questions, and then use join:

delete erq
    from exam_relatedquestion erq join
         (select erq2.*,
                 (@rn := if(@q = erq2.from_question_id_id, @rn + 1,
                            if(@q := erq2.from_question_id_id, 1, 1)
                           )
                 ) as seqnum
          from exam_relatedquestion erq2 cross join
               (select @rn := 0, @q := -1) params
          order by erq2.from_question_id_id, score desc
         ) erq2
         on erq2.id = erq.id
    where rn > 7;