Order and restriction

Question

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

CHOOSE * FROM ORDER BY population population DESC LIMIT 5

This gives me the largest cities ordered by the population, but I want the same cities to be ordered by name. Is there an easy way to do this (without going to a subquery or sorting cities after PHP)?

Thanks.

Answer 1

I think you want:

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

Answer 2

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

Answer 3

SELECT * FROM ORDER BY population DESC, LIMIT 5

? ,

Answer 4

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

.

.

Answer 5

:

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

EDIT: , . ? , ( ).

Answer 6

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 discarded when the connection is closed, or they can be dropped manually. Temporary tables are not visible from other joins. The usual way would be (but not yet supported):

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)