CODEWITHSUNDEEP
mysqlsqljoin
CodeCrack
CodeCrack

Reputation: 5373

How to get top 5 selected items with mysql query?

I have a table submitted_answers and a table answers

in submitted_answers I have id, and ans field ans is a varchar(255) field that contains comma delimited data such as "18,19,20,22,23,25"

all those numbers are correlated to ids in answers table Example table structure

submitted_answers
id (int)    ans(varchar(255))
1           "18, 19"
2           "21, 22"

answers
id  ans
18  "Money"
19  "Gold"
20  "Whatever"
21  "Whatever2"

How can i join these table and see which top 5 items appear the most in submitted_answers.ans field.

Upvotes: 0

Views: 2391

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Admittedly, your database structure is aweful. You should not be representing lists of ids as comma-delimited strings in a relational database.

That said, you can do what you want with a clever use of the on clause:

select a.id, a.ans, count(*) as cnt
from submitted_answers sa join
     answers a
     on concat(', ', sa.ans, ', ') like concat('%, ', a.id, ', %')
group by a.id, a.ans
order by cnt desc
limit 5

Upvotes: 1

What have you tried
What have you tried

Reputation: 11148

Here you go:

    SELECT answer_num, COUNT(*) AS magnitude 
FROM answers 
GROUP BY answer_num 
ORDER BY magnitude DESC
LIMIT 5

Working example: http://sqlfiddle.com/#!2/80a68/2

Notice the way I setup the database there, you may want to consider this type of setup for the many to many relationship of User and user answer.

Upvotes: 0

Related Questions