Ordering and limit

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开发者_如何学Go (without turning to a subquery or sorting the cities afterwards with PHP)?

I think what you want is this:

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

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

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).

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.

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)

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

Did you try this? I think this can work