CODEWITHSUNDEEP
sqlh2
theAnonymous
theAnonymous

Reputation: 1804

SQL - how to get matching survey results?

How do I grab entries by the matching criteria?

If every question has 5 possible answers, how do I grab entries and sort them in a way that the best matches come first by some percentage (let's say 60%), or only the entries that fail by the percentage (40%)?

SELECT * FROM SurveyAnswers WHERE(A1 = 1 OR A2 = 2 OR A3 = 3 OR A4 = 4 ... ) does not perform the percentage checks.

DB used is H2.

UserName(PK)    A1  A2  A3  A4  A5
User1           1   2   1   4   5
User2           2   5   2   3   5
User3           3   2   1   4   5

So, I want to search for the criteria A1 = 1, A2 = 2, A3 = 3, A4 = 4, A5 = 5 but return the entry if and only if it matches at least 60% of the criteria.

The returned rows should be User1, and User3. User 1's answers match 80% and User3's answers match 60%

Upvotes: 1

Views: 54

Answers (1)

sagi
sagi

Reputation: 40481

If you meant that the match for A1 is 1 and for A2 is 2 and so on.. use CASE EXPRESSION:

SELECT t.userName
FROM YourTable t
WHERE (CASE WHEN t.A1 = 1 THEN 1 ELSE 0 END +
       CASE WHEN t.A2 = 2 THEN 1 ELSE 0 END +
       CASE WHEN t.A3 = 3 THEN 1 ELSE 0 END +
       CASE WHEN t.A4 = 4 THEN 1 ELSE 0 END +
       CASE WHEN t.A5 = 5 THEN 1 ELSE 0 END) >= 3

Upvotes: 2

Related Questions