How do I select the top 5 results that have the maximum entries

Question

I'm working on a reporting software. I'm using MySQL as its Database backend. In the database there's a column called "location". The location column has a lot of values and I want to display the top 5 locations that have the maximum entries.

For eg. there are 500 entries. Out of which, 100 are from Delhi, 150 from Mumbai, 80 from Jaipur, 60 from Hyderabad and so on. Now, I want to create a table of "Top Locations".

I have tried "select * from table_name limit 5" but this returns the last 5 entries.

Is there any method I'm missing?

Make use of MySQL's aggregate functions http://www.mysqltutorial.org/mysql-aggregate-functions.aspx — Funk Forty Niner, Jan 01 '15 at 17:38

score 0 · Accepted Answer · answered Jan 01 '15 at 17:41

0

I'm on a phone so sorry for the brevity. You can use GROUP BY to accomplish this. Something like:

SELECT location, COUNT(1) AS ct 
FROM table_name 
GROUP BY location 
ORDER BY ct DESC 
LIMIT 5

answered Jan 01 '15 at 17:41

Mark Madej

1,752
1
14
19

The above one worked. Thank you so much! You just saved hours! ; – user2443840 Jan 01 '15 at 17:45
Any time @user2443840 :) – Mark Madej Jan 01 '15 at 17:46

How do I select the top 5 results that have the maximum entries

1 Answers1