pinaki pinaki - 1 year ago 74
MySQL Question

Mysql find the range with most elements

I have a simple mysql table with name and age columns. I need to find the age range (say with length 5) which contains the most number of records. Please note that the range can be from anything to anything (like 1 to 5 years or 2 to 6 years). I have created a sqlfiddle for the same at!2/a65265/1

I have tried using DIV and searched through the forums, but the closest i can get is predefined ranges like age 5-10, 10-15 etc. I need a more generic solution for all possible age ranges.

Answer Source
select          5 *  floor((t.age-o.offset)/5)      + o.offset      as from_age 
               ,5 * (floor((t.age-o.offset)/5) + 1) + o.offset - 1  as to_age
               ,count(*)                                            as cnt

from            test as t cross join (select 0 as offset union all select 1 union all select 2 union all select 3 union all select 4) as o

group by        o.offset 

order by        cnt desc

limit           1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download