SQLite How to find the most common occurrences of a value

Question

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?

Answer 1

Query like

SELECT x,COUNT(x) AS cnt FROM a
GROUP BY x
ORDER BY cnt DESC;

and stop processing the result rows when cnt changes.

Answer 2

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