CODEWITHSUNDEEP
mysqlsorting
RC.
RC.

Reputation: 131

MySQL - Selecting the top occurring entries

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!

Upvotes: 3

Views: 907

Answers (3)

Mark Byers
Mark Byers

Reputation: 838796

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');

Upvotes: 2

Salil
Salil

Reputation: 47522

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

Upvotes: 0

artemb
artemb

Reputation: 9381

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

not sure though

Upvotes: 0

Related Questions