Ordering and limit

Question

In a table containing cities I want to get the five biggest cities and order them by name:

SELECT * FROM cities ORDER BY population DESC LIMIT 5

That gets me the biggest cities ordered by population, but I want the same cities ordered by name. Is there an easy way to do this (without turning to a subquery or sorting the cities afterwards with PHP)?

Thanks.

Answer 1

SELECT * FROM cities ORDER BY population desc, name LIMIT 5

Answer 2

SELECT * FROM cities ORDER BY population DESC, name LIMIT 5

Did you try this? I think this can work

Answer 3

Simply do

SELECT y.*
  FROM (SELECT name FROM cities ORDER BY population DESC LIMIT 5) AS x,
       cities AS y
 WHERE x.name = y.name
 ORDER BY y.name

That's all there's to it.

Cheers.

Answer 4

You are going to need a subquery:

SELECT a.* 
FROM (
    SELECT * 
    FROM cities 
    ORDER BY population DESC 
    LIMIT 5
) a 
ORDER BY name;

EDIT: Just saw that you don't want a subquery. Why not? That is an efficient query that will return very quickly (there should be an index on population with or without a subquery).

Answer 5

mysql> create temporary table temp ( ID int );
mysql> insert into temp select ID from cities order by population desc limit 5;
mysql> select a.* from cities a,temp b where a.ID=b.ID order by Name;

Temporary tables are dropped when the connection is closed, or they can be dropped manually. Temporary tables cannot be seen from other connections. The normal way would be (but it is unsupported yet):

mysql> select * from cities where ID in (select ID from cities order by population desc limit 5) order by Name;

But the answer is:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

(Tried with 5.0.5)

Answer 6

I think what you want is this:

( SELECT * FROM cities ORDER BY population DESC LIMIT 5 ) ORDER BY name;