MySQL - Selecting the top occurring entries

Question

Hi, Should be a simple one.

Database is mydb. One of the columns is mydata.

What SELECT query do I need in order to select the top 3 occurring results from mydata, but sorted alphabetically?

For example, if my data is this:

mydata
======
kilo (x 1 occurrence)
lima (x 9 occurrences)
golf (x 5 occurrences)
echo (x 9 occurrences)
zulu (x 8 occurrences)

How do I get it to return "echo, lima, zulu", which are the top three frequently occurring entries sorted alphabetically? Thanks!

EDIT: Just to add, they need to be distinct entries. Thanks!

Answer 1

SELECT mydata 
FROM mytable 
GROUP BY mydata 
ORDER BY count(id), mydata

not sure though

Answer 2

Use an inner select to select the results you want, and the outer select to put them into alphabetical order.

SELECT mydata
FROM (
    SELECT mydata
    FROM mytable
    GROUP BY mydata
    ORDER BY COUNT(mydata) DESC
    LIMIT 3
) AS T1
ORDER BY mydata

Result:

'echo'
'lima'
'zulu'

Test data:

CREATE TABLE mytable (mydata VARCHAR(100) NOT NULL);
INSERT INTO mytable (mydata) VALUES
    ('kilo'),
    ('lima'), ('lima'), ('lima'), ('lima'), ('lima'), ('lima'), ('lima'), ('lima'), ('lima'),
    ('golf'), ('golf'), ('golf'), ('golf'), ('golf'),
    ('echo'), ('echo'), ('echo'), ('echo'), ('echo'), ('echo'), ('echo'), ('echo'), ('echo'),
    ('zulu'), ('zulu'), ('zulu'), ('zulu'), ('zulu'), ('zulu'), ('zulu'), ('zulu');

Answer 3

SELECT mydata
FROM mytable
GROUP BY mydata
ORDER BY COUNT(mydata) DESC, mydata
LIMIT 3