I have a table wherein users save their answers to certain questions.
For Eg: There are 5 question:
User "A" and User "B" have same answers to question numbers: 1, 4 and 5. ie 3 same answers.
User "A" and User "C" have same answers to question number 1, 2, 4 and 5. ie. 4 same answers
The mysql query should output me results in following order.
The output should have the maximum matching answers first.
Is it possible to do this at database level. The reason I want to do this at database level, is the frontend query asks for selection of user that have atleast 4 matching answers, or atleast 7 matching answers. There are huge number of users.
I am open to creating new data structures to save data in a format, that will make these calculations possible.
Looking forwards towards suggestions and guidance.
Thank You.
There is nothing like a correct answer. Each answer can be a correct answer. Question will be like " Which colour do you Like ", wherein all answers are correct. We need to match number of same answers between a user, and all other users. The results, we want to sort by ones having largest number of matches.
When saving the answers to the db you might consider an additional field that stores the number of correct answers (e.g integer of 4, 5 etc.) which you can later sort on when returning records.