ARC ARC - 11 days ago 6
MySQL Question

List by most common in MySQL

Put basically I have an MySQL database with multiple columns such as "name", "description" and "tags". If my user searched for a specific word such as "apple", how would I go through all of the columns and return the rows in the order of most common to least common.

An example would be: the user searches for "apple" and the first result with that will show at the top of the list (as my current system does) however "banana" has more occurrences of apple in the description and tags so I want that to be at the top.

Answer

You could just count it using the built in count function in mysql

The following example counts the amount of times " UserId " have been used but you can modify it to your need.

Mysql COUNT documentation

SELECT UserId, COUNT(*)
FROM badges
GROUP BY UserId
ORDER BY COUNT(*) DESC