CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
Jeff
Jeff

Reputation: 6663

mysql order by with limit

I have a query where I want to get the 20 most popular locations from a table, and then order them alphabetically. I'm wondering if there's a cleaner or more efficient way to do this?

SELECT
  city
FROM (
  SELECT 
    city, 
    count(*) AS cnt
  FROM locations 
  GROUP BY city
  ORDER BY cnt DESC
  LIMIT 20
) s ORDER BY city;

Upvotes: 0

Views: 3817

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Slightly cleaner:

SELECT city FROM (
  SELECT city FROM locations 
  GROUP BY city
  ORDER BY count(*) DESC
  LIMIT 20
) s ORDER BY city

You don't need to retrieve the count(*) if you're not going to use it.

Upvotes: 3

Related Questions