Question 21218 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get maximum appearance count of number from comma separated number string from multiple rows in MySQL?

My MySQL table having column with comma separated numbers. See below example –

| style_ids  |
| ---------- |
| 5,3,10,2,7 |
| 1,5,12,9   |
| 6,3,5,9,4  |
| 8,3,5,7,12 |
| 7,4,9,3,5  |

So my expected result should have top 5 numbers with maximum appearance count in descending order as 5 rows as below –

| number | appearance_count_in_all_rows | 
| -------|----------------------------- |
| 5      | 5                            |
| 3      | 4                            |
| 9      | 3                            |
| 7      | 2                            |
| 4      | 2                            |

Is it possible to get above result by MySQL query ?

Tags:

2

Answers


  1. 0

    As already alluded to in the comments, this is a really bad idea. But here is one way of doing it –

    WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 20
), tbl (style_ids) AS (
    SELECT '5,3,10,2,7' UNION ALL
    SELECT '1,5,12,9' UNION ALL
    SELECT '6,3,5,9,4' UNION ALL
    SELECT '8,3,5,7,12' UNION ALL
    SELECT '7,4,9,3,5'
)
SELECT seq.n, COUNT(*) appearance_count_in_all_rows
FROM seq
JOIN tbl ON FIND_IN_SET(seq.n, tbl.style_ids)
GROUP BY seq.n
ORDER BY appearance_count_in_all_rows DESC
LIMIT 5;

    Just replace the tbl cte with your table.

    Login or Signup to reply.
  2. 0

    As already pointed out you should fix the data if possible.

    For further details read Is storing a delimited list in a database column really that bad?.

    You could use below answer which is well explained here and a working fiddle can be found here.

    Try,

    select distinct_nr,count(distinct_nr) as appearance_count_in_all_rows 
from   ( select substring_index(substring_index(style_ids, ',', n),  ',',  -1) as distinct_nr
         from test
         join numbers on char_length(style_ids) - char_length(replace(style_ids, ',', ''))  >= n - 1 
        ) x
group by distinct_nr
order by appearance_count_in_all_rows desc  ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search