| id | category |
|---|---|
| 1 | eat,drink |
| 2 | drink,cup |
| 3 | eat,take |
| 4 | tea,eat |
i need to order records which have max number of category repeated by category column, ex- eat are repeated 3 times then it is comes first order and then drink comes then tea or cup comes.. thanks in advance..
As Gordon suggested data model should be changed to get most out of RDBMS. But what you are looking for can be achieved through below query:
select category,count(*) category_count from(
select t.id, j.category
from tablename t
join json_table(
replace(json_array(t.category ), ',', '","'),
'$[*]' columns (category varchar(50) path '$')
) j
)t
group by category
order by count(*) desc;
Output:
