SQLite How to find the most common occurrences of a value

sql, sqlite

Solution

This takes care of multiple values having maximum occurence

SELECT X FROM yourTable
GROUP BY X
HAVING COUNT(*) = (
                   SELECT MAX(Cnt) 
                   FROM(
                         SELECT COUNT(*) as Cnt
                         FROM yourTable
                         GROUP BY X
                        ) tmp
                    )

SQL FIDDLE

Problem

Say I have a table A with attributes X How do I find the X's with the largest occurrences? (There can be multiple that have the same highest occurrence) i.e. table A ``` X -- 'a' 'b' 'c' 'c' 'b' ``` I would want to get back ``` X -- 'b' 'c' ``` I can't use the keyword ALL in Sqlite so I'm at a loss. I thought of getting the counts of each X and then sorting it and then somehow using ORDER BY DESC so that the biggest is at the top and then LIMIT with a comparison to check if values below the first tuple are equal (which means they are just as common) but I'm not sure about LIMIT syntax and if I can have a condition like that Please give a hint and not the answer, are there any resources I can reference so I can figure out a way?

Original source