CODEWITHSUNDEEP
mysqlsql-order-bylimit
Jonas
Jonas

Reputation: 51

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 (without turning to a subquery or sorting the cities afterwards with PHP)?

Upvotes: 5

Views: 357

Answers (6)

José Mélançon
José Mélançon

Reputation: 106

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

Did you try this? I think this can work

Upvotes: 0

ggiroux
ggiroux

Reputation: 6724

I think what you want is this:

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

Upvotes: 9

Notinlist
Notinlist

Reputation: 16640

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)

Upvotes: 0

aefxx
aefxx

Reputation: 25249

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.

Upvotes: 0

Jackson Miller
Jackson Miller

Reputation: 1510

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

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

Upvotes: 1

Related Questions